Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit913bbd8

Browse files
committed
Improve the handling of result type coercions in SQL functions.
Use the parser's standard type coercion machinery to convert theoutput column(s) of a SQL function's final SELECT or RETURNINGto the type(s) they should have according to the function's declaredresult type. We'll allow any case where an assignment-levelcoercion is available. Previously, we failed unless the requiredcoercion was a binary-compatible one (and the documentation ignoredthis, falsely claiming that the types must match exactly).Notably, the coercion now accounts for typmods, so that cases wherea SQL function is declared to return a composite type whose columnsare typmod-constrained now behave as one would expect. Arguablythis aspect is a bug fix, but the overall behavioral change hereseems too large to consider back-patching.A nice side-effect is that functions can now be inlined in afew cases where we previously failed to do so because of typemismatches.Discussion:https://postgr.es/m/18929.1574895430@sss.pgh.pa.us
1 parent8dd1511 commit913bbd8

File tree

7 files changed

+656
-332
lines changed

7 files changed

+656
-332
lines changed

‎doc/src/sgml/xfunc.sgml

Lines changed: 21 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -388,28 +388,26 @@ $$ LANGUAGE SQL;
388388
</para>
389389

390390
<para>
391-
A <acronym>SQL</acronym> function must return exactly its declared
392-
result type. This may require inserting an explicit cast.
391+
If the final <literal>SELECT</literal> or <literal>RETURNING</literal>
392+
clause in a <acronym>SQL</acronym> function does not return exactly
393+
the function's declared result
394+
type, <productname>PostgreSQL</productname> will automatically cast
395+
the value to the required type, if that is possible with an implicit
396+
or assignment cast. Otherwise, you must write an explicit cast.
393397
For example, suppose we wanted the
394398
previous <function>add_em</function> function to return
395-
type <type>float8</type> instead.This won't work:
399+
type <type>float8</type> instead.It's sufficient to write
396400

397401
<programlisting>
398402
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
399403
SELECT $1 + $2;
400404
$$ LANGUAGE SQL;
401405
</programlisting>
402406

403-
even though in other contexts <productname>PostgreSQL</productname>
404-
would be willing to insert an implicit cast to
405-
convert <type>integer</type> to <type>float8</type>.
406-
We need to write it as
407-
408-
<programlisting>
409-
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
410-
SELECT ($1 + $2)::float8;
411-
$$ LANGUAGE SQL;
412-
</programlisting>
407+
since the <type>integer</type> sum can be implicitly cast
408+
to <type>float8</type>.
409+
(See <xref linkend="typeconv"/> or <xref linkend="sql-createcast"/>
410+
for more about casts.)
413411
</para>
414412
</sect2>
415413

@@ -503,23 +501,24 @@ $$ LANGUAGE SQL;
503501
<listitem>
504502
<para>
505503
The select list order in the query must be exactly the same as
506-
that in which the columns appear in thetable associated
507-
with the composite type.(Naming the columns, as we did above,
504+
that in which the columns appear in thecomposite type.
505+
(Naming the columns, as we did above,
508506
is irrelevant to the system.)
509507
</para>
510508
</listitem>
511509
<listitem>
512510
<para>
513-
We must ensure each expression's typematches the corresponding
514-
column of the composite type, inserting a cast if necessary.
511+
We must ensure each expression's typecan be cast to that of
512+
the correspondingcolumn of the composite type.
515513
Otherwise we'll get errors like this:
516514
<screen>
517515
<computeroutput>
518-
ERROR: function declared to return emp returns varchar instead of text at column 1
516+
ERROR: return type mismatch in function declared to return emp
517+
DETAIL: Final statement returns text instead of point at column 4.
519518
</computeroutput>
520519
</screen>
521-
As with the base-type case, thefunction will not insertany casts
522-
automatically.
520+
As with the base-type case, thesystem will not insertexplicit
521+
castsautomatically, only implicit or assignment casts.
523522
</para>
524523
</listitem>
525524
</itemizedlist>
@@ -542,8 +541,7 @@ $$ LANGUAGE SQL;
542541
Another example is that if we are trying to write a function that
543542
returns a domain over composite, rather than a plain composite type,
544543
it is always necessary to write it as returning a single column,
545-
since there is no other way to produce a value that is exactly of
546-
the domain type.
544+
since there is no way to cause a coercion of the whole row result.
547545
</para>
548546

549547
<para>
@@ -1263,7 +1261,7 @@ SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
12631261
Without the typecast, you will get errors like this:
12641262
<screen>
12651263
<computeroutput>
1266-
ERROR: could not determine polymorphic type because input has type"unknown"
1264+
ERROR: could not determine polymorphic type because input has type unknown
12671265
</computeroutput>
12681266
</screen>
12691267
</para>

‎src/backend/catalog/pg_proc.c

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -923,6 +923,8 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
923923
* verify the result type.
924924
*/
925925
SQLFunctionParseInfoPtrpinfo;
926+
Oidrettype;
927+
TupleDescrettupdesc;
926928

927929
/* But first, set up parameter information */
928930
pinfo=prepare_sql_fn_parse_info(tuple,NULL,InvalidOid);
@@ -943,9 +945,12 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
943945
}
944946

945947
check_sql_fn_statements(querytree_list);
946-
(void)check_sql_fn_retval(funcoid,proc->prorettype,
947-
querytree_list,
948-
NULL,NULL);
948+
949+
(void)get_func_result_type(funcoid,&rettype,&rettupdesc);
950+
951+
(void)check_sql_fn_retval(querytree_list,
952+
rettype,rettupdesc,
953+
false,NULL);
949954
}
950955

951956
error_context_stack=sqlerrcontext.previous;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp