Using %rowtype when returning in a PostgreSQL function
About %ROWTYPE
The %ROWTYPE
construct is only good for portability. Rarely useful, since PL/pgSQL functions are hardly portable to begin with.
If you are going to use it, it's only meant for variable declaration inside PL/pgSQL function, not to declare the RETURN
type, which is actually part of the outer SQL syntax.
(Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write
%ROWTYPE
or not. But the form with%ROWTYPE
is more portable.)
Answer
This would achieve what you seem to be trying:
CREATE OR REPLACE FUNCTION test_plpgsql(_n int)
RETURNS tbl AS
$func$
BEGIN
RETURN (SELECT t FROM tbl t where tbl_id = _n); -- selecting the whole row
END
$func$ LANGUAGE plpgsql;
Call:
SELECT * FROM test_plpgsql(1);
But if it's as simple as that, use a simpler SQL function instead:
CREATE OR REPLACE FUNCTION test_sql(_n int)
RETURNS SETOF tbl AS
$func$
SELECT * FROM tbl WHERE tbl_id = _n; -- Requires Postgres 9.3; or use $1
$func$ LANGUAGE sql;
Call:
SELECT * FROM test_sql(1);
Your original example was twisted and incorrect in too many places. Search for more plpgsql examples to get a grasp on basic syntax.
Related videos on Youtube
Douglas
Updated on June 25, 2022Comments
-
Douglas almost 2 years
If I have a function that returns only one row with some columns from a table do I need to add
%rowtype
in the function return declaration?CREATE OR REPLACE FUNCTION test(int n) RETURNS tableName%rowtype AS $BODY$ DECLARE r tableName%rowtype; BEGIN select a,b,c into r from tableName where d=n; return r; $BODY$ END;