Trying to combine postgres functions returning tables into 1 function returning json

Question

I have multiple functions that all return the same data structure in a table. I have 1 function that calls all of them via a UNION and it returns the results in a table format. How do I get that 1 function to return the results from all the subfunctions in a json?

I tried doing:

SELECT row_to_json(t) from (select col1, col2, col3 from db.subfunction1())
UNION
SELECT row_to_json(t) from (select col1, col2, col3 from db.subfunction2())
UNION
SELECT row_to_json(t) from (select col1, col2, col3 from db.subfunction3());

and got

ERROR: Could not identify an equality operator for type json LINE 1: select row_to_json(t) from (select.......... ^ SQL state: 42883 Character: 8

OKay, so I run the following: Select * from db.subfunction1();

The result is a table

data type	A date	some text	Another text field
Something	2021-01-31	detail info	ip4: 5663773;

Each function does the same thing. I want the parent function to convert the output to JSON.

You have more ( than ) is this correct ? Please, can you share expected results, and the definition of the functions db.subfunction1(), db.subfunction2() and db.subfunction3() — Luuk
– Luuk, Commented Feb 8, 2023 at 20:23
Each of the child functions return a text, date, text, and text fields in that order. All of them look at different schemas with tables that are identical. I did not notice that the parentheses are missing in my question, just a typo on my part. I want the output from those child functions (defined as returning a table) to be returned in json format from the parent function. — Doug
– Doug, Commented Feb 9, 2023 at 15:32
Help us help you, and share the output from select col1, col2, col3 from db.subfunction1() — Luuk
– Luuk, Commented Feb 9, 2023 at 16:37
Try UNION ALL instead of UNION which won't need an equality operator. Unless you actually need to filter for duplicates? — Bergi
– Bergi, Commented Feb 9, 2023 at 20:30
I ended up using the UNION ALL and got some more assistance from stackoverflow.com/questions/24220409/…. Overthinking things is a bad habit of mine. Simple SQL function was all I needed to call the other 3 child functions and I got the output I needed. Thanks to EVERYONE here who helped me. MUCH APPRECIATED!!! — Doug
– Doug, Commented Feb 15, 2023 at 19:40

Luuk · Accepted Answer · 2023-02-09 20:06:16Z

0

Your question is still pretty unclear, but let's try to repeat the row_to_json() on the results of the 3 unions:

WITH abc as (
   SELECT 'Something' as col1,  '2021-01-31' as col2,   'detail info' as col3),
  def as (
   select 1 as i,row_to_json(abc) as j from abc
     union all
   select 2,row_to_json(abc) from abc
     union all
   select 3,row_to_json(abc) from abc
  )
select row_to_json(x) from (
select def.j as a, d2.j as b, d3.j as c
from def
inner join def d2 on d2.i=2
inner join def d3 on d3.i=3 
where def.i=1) x;

see: DBFIDDLE

result:

{"a":{"col1":"Something","col2":"2021-01-31","col3":"detail info"},"b":{"col1":"Something","col2":"2021-01-31","col3":"detail info"},"c":{"col1":"Something","col2":"2021-01-31","col3":"detail info"}}

answered Feb 9, 2023 at 20:06

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

Trying to combine postgres functions returning tables into 1 function returning json

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related