How to form a key/value array using postgreSQL?

Question

I currently have two tables:

tblFoo
foo_id   foo_text
1        foo

and

tblBar
bar_id    foo_id     bar_text
100       1          hello
101       1          world

I am trying to return a polymorphic row type using a function, a la:

CREATE OR REPLACE FUNCTION fetch_foo(p_foo_id int) RETURNS record as $$
DECLARE 
    rec record;
BEGIN
    SELECT
        F.foo_id,
        foo_text,
        array_agg(bar_text) as bar_text
    FROM tblFoo F
    LEFT JOIN tblBar B
    ON F.foo_id = B.foo_id
    WHERE F.foo_id = p_foo_id
    GROUP BY F.foo_id
    INTO rec;
RETURN rec;
END
$$ LANGUAGE plpgsql;

Currently, this is returning a single concatenated field called 'fetch_foo' of (1,"foo","{hello,world}"). I have read the reply at Return multiple fields as a record in PostgreSQL with PL/pgSQL and understood that I shouldn't need to create a type here to return a record with separate fields. What am I overlooking?

Thanks.

user330315 · Accepted Answer · 2012-07-06 14:16:35Z

2

You don't need to define a record. It's a lot simpler than that, just define it as "returns table" and use SQL not PL/pgSQL.

CREATE OR REPLACE FUNCTION fetch_foo(p_foo_id int) 
     RETURNS table(foo_id int, foo_text text, bar_text text[]) 
AS 
$$
   SELECT
        F.foo_id,
        foo_text,
        array_agg(bar_text) as bar_text
    FROM tblFoo F
    LEFT JOIN tblBar B
    ON F.foo_id = B.foo_id
    WHERE F.foo_id = $1
    GROUP BY F.foo_id
$$ 
LANGUAGE sql;

answered Jul 6, 2012 at 14:16

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Huxton Over a year ago

I think circular might be after splitting the fields out - reread your linked answer circular. In particular: SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);

Collectives™ on Stack Overflow

How to form a key/value array using postgreSQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related