Declaring a tuple structure of a record in PL / pgSQL

Question

I cannot find anything in the PostgreSQL documentation that shows how to declare a record or string while declaring a tuple structure. If you do not define the structure of the tuple, you get the error "The structure of the tuple of an unassigned record is undefined".

This is what I'm doing now that works great, but there should be a better way to do this.

CREATE OR REPLACE FUNCTION my_func() RETURNS TABLE ( "a" integer, "b" varchar ) AS $$ DECLARE r record; BEGIN CREATE TEMP TABLE tmp_t ( "a" integer, "b" varchar ); -- Define the tuple structure of r by SELECTing an empty row into it. -- Is there a more straight-forward way of doing this? SELECT * INTO r FROM tmp_t; -- Now I can assign values to the record. ra := at.something FROM "another_table" at WHERE at.some_id = 1; -- A related question is - how do I return the single record 'r' from -- this function? -- This works: RETURN QUERY SELECT * FROM tmp_t; -- But this doesn't: RETURN r; -- ERROR: RETURN cannot have a parameter in function returning set END; $$ LANGUAGE plpgsql; 

Answer 1

You mix syntax to return SETOF values ​​with syntax to return a single row or value .

- A related question: how to return a single record 'r' from

When you declare a function using RETURNS TABLE , you must use RETURN NEXT in the body to return a string (or scalar value). And if you want to use the record variable so that it must match the return type. See Code Examples below.

Returns a single value or string

If you just want to return a single row, there is no need for an undefined entry. @Kevin has already demonstrated two ways. I will add a simplified version with OUT parameters:

 CREATE OR REPLACE FUNCTION my_func(OUT a integer, OUT b text) AS $func$ BEGIN a := ...; b := ...; END $func$ LANGUAGE plpgsql; 

You do not even need to add RETURN; to the function body, the value of the declared OUT parameters will be automatically returned at the end of the function - NULL for any parameter that has not been assigned.
And you do not need to declare RETURNS RECORD , because it is already visible from the OUT parameters.

Returns a rowset

If you really want to return multiple rows (including the option for 0 or 1 rows), you can define the return type as RETURNS ...

• SETOF some_type , where some_type can be any registered scalar or composite type.

• TABLE (col1 type1, col2 type2) - ad-hoc type definition.

• SETOF record plus OUT parameters for determining column names and types.
  100% equivalent to RETURNS TABLE .

• SETOF record without further definition. But then the returned rows are undefined , and you need to include a column definition list with each call (see Example).

Record Type Guide :

Writing variables is similar to string type variables, but they have no predefined structure. They take the actual row structure of the string that they assign during the SELECT or FOR command.

There is more, read the manual.

You can use a record variable without assigning a specific type, you can even return such records undefined:

 CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF record AS $func$ DECLARE r record; BEGIN r := (1::int, 'foo'::text); RETURN NEXT r; -- works with undefined record r := (2::int, 'bar'::text); RETURN NEXT r; END $func$ LANGUAGE plpgsql; 

Call:

 SELECT * FROM my_func() AS x(a int, b text); 

But this is very cumbersome as you need to provide a list of column definitions with every call. Usually it can be replaced with something more elegant:

• If you know the type at the time the function was created, declare it immediately ( RETURNS TABLE or friends).

 CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF tbl_or_type AS $func$ DECLARE r tbl_or_type; BEGIN SELECT INTO tbl_or_type * FROM tbl WHERE id = 10; RETURN NEXT r; -- type matches SELECT INTO tbl_or_type * FROM tbl WHERE id = 12; RETURN NEXT r; -- Or simpler: RETURN QUERY SELECT * FROM tbl WHERE id = 14; END $func$ LANGUAGE plpgsql; 

• If you know the type during a function call, there are more elegant ways to use polymorphic types :
  Refactoring the PL / pgSQL function to return the output of various SELECT queries

In your question it is not clear what you need.

Answer 2

There may be some way to avoid an explicit type declaration, but because of what I can think of, you can do the following:

 CREATE TYPE my_func_return AS ( a integer, b varchar ); CREATE OR REPLACE FUNCTION my_func() RETURNS my_func_return AS $$ DECLARE r my_func_return; BEGIN SELECT 1, 'one' INTO ra, rb; RETURN r; END; $$ LANGUAGE plpgsql; 

Oh, I almost forgot the simplest way to do this:

 CREATE OR REPLACE FUNCTION my_func2(out a int, out b text) RETURNS RECORD AS $$ BEGIN SELECT 1, 'one' INTO a, b; RETURN; END; $$ LANGUAGE plpgsql; 

Answer 3

It is much easier to use the OUT parameters rather than the write. If iteratively constructing a recordset (table) uses RETURN NEXT . If you generate a query, use RETURN QUERY . Cm:

fooobar.com/questions/116557 / ...

and

http://www.postgresql.org/docs/current/static/plpgsql-declarations.html http://www.postgresql.org/docs/current/static/sql-createfunction.html http: //www.postgresonline. com / journal / archives / 129-Use-of-OUT-and-INOUT-Parameters.html

Think:

 CREATE OR REPLACE FUNCTION my_func(OUT a integer, OUT b varchar) RETURNS SETOF RECORD AS $$ BEGIN -- Assign a and b, RETURN NEXT, repeat. when done, RETURN. END; $$ LANGUAGE 'plpgsql';