0

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?

1

1 Answer 1

1

One method would be to convert the row to json:

    select row_to_json(c)
    from chain c  
Sign up to request clarification or add additional context in comments.

1 Comment

You would just use row_to_json() on each subquery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.