Are there any formal differences between PostgreSQL functions with OUT parameters and TABLE results?

Question

Consider these two PostgreSQL functions:

CREATE OR REPLACE FUNCTION f_1 (v1 INTEGER, v2 OUT INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_2 (v1 INTEGER) RETURNS TABLE(v2 INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; 

In any "normal" procedural SQL language (such as Transact-SQL), the two types of functions will be completely different. f_1 will actually be a procedure, while f_2 will be a table function. In SQL Server, the latter is returned from INFORMATION_SCHEMA.ROUTINES as follows:

 SELECT r.routine_schema, r.routine_name FROM information_schema.routines r WHERE r.routine_type = 'FUNCTION' AND r.data_type = 'TABLE' 

In PostgreSQL, this does not work. The following query shows that there is no significant difference between the signatures f_1 and f_2 :

 SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type FROM information_schema.routines r JOIN information_schema.parameters p USING (specific_catalog, specific_schema, specific_name); 

The above gives:

 routine_name | data_type | parameter_name | data_type -------------+-----------+----------------+---------- f_1 | integer | v1 | integer f_1 | integer | v2 | integer f_2 | integer | v1 | integer f_2 | integer | v2 | integer 

Not everything gets better if I have several columns returned from functions, in which case I don't even have a formal return type anymore. Just record :

 CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_4 (v1 INTEGER) RETURNS TABLE(v2 INTEGER, v3 INTEGER) AS $$ BEGIN v2 := v1; END $$ LANGUAGE plpgsql; 

... I will get:

 routine_name | data_type | parameter_name | data_type -------------+-----------+----------------+---------- f_3 | record | v1 | integer f_3 | record | v2 | integer f_3 | record | v3 | integer f_4 | record | v1 | integer f_4 | record | v2 | integer f_4 | record | v3 | integer 

If they come from other databases, it is obvious that the meaning of the lexical signature is completely different. As an Oracle person, I expect PROCEDURES to have side effects, while FUNCTIONS has no side effects (if only in a stand-alone transaction) and can be safely embedded in SQL. I know that PostgreSQL skillfully treats all functions as tables, but I don't think it is a good idea to develop OUT parameters as table columns in any query ...

My question is:

Are there any formal differences between the two ways of declaring functions? If so, how can I detect it from INFORMATION_SCHEMA or from PG_CATALOG ?

Answer 1

\df public.f_* does this

 select n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", case when p.proisagg then 'agg' when p.proiswindow then 'window' when p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype then 'trigger' else 'normal' end as "Type" from pg_catalog.pg_proc p left join pg_catalog.pg_namespace n on n.oid = p.pronamespace where p.proname ~ '^(f_.*)$' and n.nspname ~ '^(public)$' order by 1, 2, 4; 

which returns this

  List of functions Schema | Name | Result data type | Argument data types | Type --------+------+-------------------------------+--------------------------------------------+-------- public | f_1 | integer | v1 integer, OUT v2 integer | normal public | f_2 | TABLE(v2 integer) | v1 integer | normal public | f_3 | record | v1 integer, OUT v2 integer, OUT v3 integer | normal public | f_4 | TABLE(v2 integer, v3 integer) | v1 integer | normal (4 rows) 

To drop a function, you must pass its data types ( IN and INOUT ). Then I think that the name of the function and its data types of the input arguments form their signature. And to change the returned data type, you must first drop it and recreate it.

Answer 2

It looks like the pg_catalog.pg_proc.proretset flag contains a hint about whether the function returns a collection (i.e. a table):

 SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type, pg_p.proretset FROM information_schema.routines r JOIN information_schema.parameters p USING (specific_catalog, specific_schema, specific_name) JOIN pg_namespace pg_n ON r.specific_schema = pg_n.nspname JOIN pg_proc pg_p ON pg_p.pronamespace = pg_n.oid AND pg_p.proname = r.routine_name WHERE r.routine_schema = 'public' AND r.routine_name IN ('f_1', 'f_2', 'f_3', 'f_4') ORDER BY routine_name, parameter_name; 

The above will give:

 routine_name | data_type | parameter_name | data_type | proretset -------------+-----------+----------------+-----------+---------- f_1 | record | v1 | integer | f f_1 | record | v2 | integer | f f_2 | record | v1 | integer | t f_2 | record | v2 | integer | t f_3 | record | v1 | integer | f f_3 | record | v2 | integer | f f_3 | record | v3 | integer | f f_4 | record | v1 | integer | t f_4 | record | v2 | integer | t f_4 | record | v3 | integer | t 

Emulation INFORMATION_SCHEMA.COLUMNS

What is it worth, and in case someone needs this crazy thing, here is a beautiful query that I came up with to emulate an implementation of SQL Server nice INFORMATION_SCHEMA.COLUMNS , which returns the columns of the table functions (this is what we really need, supporting the table-valued functions in the jOOQ code generator ):

 SELECT p.proname AS TABLE_NAME, columns.proargname AS COLUMN_NAME, ROW_NUMBER() OVER(PARTITION BY p.oid ORDER BY o.ordinal) AS ORDINAL_POSITION, format_type(t.oid, t.typtypmod) AS DATA_TYPE, information_schema._pg_char_max_length(t.oid, t.typtypmod) AS CHARACTER_MAXIMUM_LENGTH, information_schema._pg_numeric_precision(t.oid, t.typtypmod) AS NUMERIC_PRECISION, information_schema._pg_numeric_scale(t.oid,t.typtypmod) AS NUMERIC_SCALE, not(t.typnotnull) AS IS_NULLABLE FROM pg_proc p, LATERAL generate_series(1, array_length(p.proargmodes, 1)) o(ordinal), LATERAL ( SELECT p.proargnames[o.ordinal], p.proargmodes[o.ordinal], p.proallargtypes[o.ordinal] ) columns(proargname, proargmode, proargtype), LATERAL ( SELECT pg_type.oid oid, pg_type.* FROM pg_type WHERE pg_type.oid = columns.proargtype ) t WHERE p.proretset AND proargmode = 't' AND p.proname LIKE 'f%'; 

The above returns perfectly (column names are abbreviated for SO):

 table_name | column_name | ordinal | data_type | length | precision | scale | nullable f_2 | v2 | 1 | integer | | 32 | 0 | t f_4 | v2 | 1 | integer | | 32 | 0 | t f_4 | v3 | 2 | integer | | 32 | 0 | t 

Answer 3

RETURNS TABLE() actually matches the OUT parameters in combination with RETURNS SETOF ... Without the additional SETOF function with OUT parameters always returns a single row , and a function with RETURNS TABLE() can return 0-n rows .

Your example just doesn't make a difference because of how it is written.

This is reflected in the flag proretset system catalog. Guide:

The function returns a set (i.e. several values ​​of the specified data type)

In this regard, the presentation of the information_schema.routines information schema is not particularly useful. The fact that a unified standardized compromise for presenting information in a platform-independent form is hardly suitable for describing specifics in Postgres.

Functional Signature

Guide:

Two functions are considered the same if they have the same name and input , ignoring any OUT parameters

Where the "input" argument types are inlcude IN and INOUT parameters.

These two functions play an important role when working with function definitions. Guide:

 pg_get_function_arguments(func_oid) ... get argument list of function definition (with default values) pg_get_function_identity_arguments(func_oid) ... get argument list to identify a function (without default values) 

More in these related answers:

• How to get a list of function parameters (so that I can refuse the function)
• DROP FUNCTION, not knowing the number / type of parameters?