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 "ordinary" procedural SQL language (e.g. Transact-SQL), the two types of functions would be quite different. f_1
would actually be a procedure, whereas f_2
would be a table-valued function. In SQL Server, the latter is returned from INFORMATION_SCHEMA.ROUTINES
like so:
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 doesn't work, however. The following query shows that there is essentially no difference between the signatures of 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 yields:
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
Things don't get much better when I have multiple columns returned from the functions, in case of which 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'll 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 coming from other databases, clearly the intent of the lexical signature is quite different. As an Oracle person, I expect PROCEDURES
to have side-effects, whereas FUNCTIONS
don't have any side-effects (unless in an autonomous transaction) and can be safely embedded in SQL. I know that PostgreSQL cleverly treats all functions as tables, but I don't think it's a good idea to design OUT
parameters as table columns in any query...
Is there any formal difference at all between the two ways to declare functions? If there is, how can I discover it from the INFORMATION_SCHEMA
or from the PG_CATALOG
?
\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 it is necessary to pass its input (IN
and INOUT
) arguments data types. Then I guess the function name and its input arguments data types do form its signature. And to change the returned data type it is necessary to first drop it and recreate.