CODEWITHSUNDEEP
sqlpostgresql
Jwqq
Jwqq

Reputation: 1087

How to return the data from the postgres function

I am trying to return a function with the table field and values

I have a function like this:

CREATE FUNCTION books(
    id integer)
    RETURNS TABLE(
        id integer,        
        title text,        
        type text
    )
    LANGUAGE 'sql'    
AS $BODY$

WITH RECURSIVE chain(id, seq) AS (
        SELECT s.id, s.sequence
        FROM product s
        WHERE s.id = $1        
        UNION ALL
        SELECT s.id, s.sequence
        FROM product s, chain t
        WHERE s.id = t.id       
        )
        select * from chain     
$BODY$;

My query is currently returning like the following

1, book title1, new
2, book title2, new
3, book title3, old

I was hoping to return something like

id: 1, title: book title1, type: new
id: 2, title: book title2, type: new
id: 3, title: book title3, type: old

so I have the key to map the value in the api side.

Is it possible to do it?

Thanks a lot!

UPDATE:

Thanks, my actual select statement after recursive is similar like:

SELECT c.id, c.title, cu.name
FROM customer cu, book b
WHERE cu.id = b.id
UNION
(
SELECT c.id, c.title, f.cost
FROM chain c, foo f
WHERE c.id = f.id
)

How do I use row_to_json to covert them?

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method would be to convert the row to json:

    select row_to_json(c)
    from chain c

Upvotes: 1

Related Questions