Geek asks and answers

Functions cannot return individual elements of a record, right? Any workaround?

Basic things like struct in C data types exist in all popular languages, and it is expected that the functions of these languages ​​can also return struct ... And, by the principle of orthogonality, it is expected that you can access the returned itens structure.

PostgreSQL, however, did not provide access to struct itens FUNCTION ... RETURNS RECORD. It is right?

But programmers use PostgreSQL without complaint ... Is there a simple and intuitive workaround?

A similar question: PostgreSQL v9.X has a real "write array",

Typical Case Illustration

 CREATE FUNCTION foo(int) RETURNS RECORD AS $$ 
      SELECT $1 as a, 'Hello #'||$1 as b; 
 $$ LANGUAGE SQL;

 SELECT foo(6);       -- works, but I need only one item

Write access in SQL context :

 SELECT (foo(6)).a;   -- NOT works (but no ambiguity!)

 -- For syntax discussion:
 WITH f AS (SELECT foo(6) as r) SELECT r.a FROM f; -- NOT works
 -- ambiguous syntax; confused r with table, in "f.r.a", f with schema 
 -- perhaps r['a'] would be a good syntax solution

PLpgSQL:

x:=(foo(6)).a y:=foo(6); x:=y.a? , PLpgSQL , , " ":

CREATE FUNCTION bar() RETURNS text AS $F$ 
DECLARE 
   tmp record;
   s text;
BEGIN 
   -- s:=(foo(7)).b;  NOT WORKS, is like an "anonymous record" (not permitted)
   tmp := foo(6); 
   s:=tmp.b;  -- IT WORKS!! is like a "named record" (permitted)
   RETURN s||'! '||tmp.a;  --  ...works accessing any other individual itens
END;
$F$ LANGUAGE plpgsql IMMUTABLE;
0
2

returns table ( ) returns record, :

CREATE FUNCTION foo(int) RETURNS table (a int, b text) 
AS 
$$ 
  SELECT $1 as a, 'Hello #'||$1 as b; 
$ LANGUAGE SQL;

:

select b
from foo(6);

"" "", type :

create type foo_return as (a int, b text);
CREATE FUNCTION foo(int) RETURNS foo_return
AS 
$$ 
  SELECT $1, 'Hello #'||$1;
$$ LANGUAGE SQL;

:

select b
from foo(6);

, , "C" ( )

CREATE FUNCTION foo(p1 int, out a int, out b text)
AS
$$ 
  SELECT $1, 'Hello #'||$1;
$$ 
LANGUAGE SQL;

from:

select (foo(1)).b;
+1

?

select a 
from foo(6) s(a int, b text);
+2

More articles:


All Articles