How to write a select function for converting table data to JSON?

Question

I am using postgresql sp function for getting table data in JSON format. But i am getting this error.

structure of query does not match function result type DETAIL: Returned type json does not match expected type uuid in column 1. CONTEXT: SQL statement "SELECT array_to_json(array_agg(row_to_json(tech_alias))) FROM (select * from "Dcep"."Technologies") tech_alias" PL/pgSQL function "Dcep".jsontechnologies() line 3 at RETURN QUERY SQL state: 42804

I tried this function

CREATE OR REPLACE FUNCTION "Dcep".jsontechnologies()
RETURNS TABLE
(id uuid, technologyname text, 
technologytype text,
 status text,
createddate timestamp without time zone,
 createdby text, createdip text,
lastmodifieddate timestamp without time zone,
lastmodifiedby text,
 lastmodifiedip text, componentid uuid
) LANGUAGE 'plpgsql' AS 
$BODY$
BEGIN
RETURN Query 
SELECT array_to_json(array_agg(row_to_json(tech_alias)))
FROM (select * from "Dcep"."Technologies") tech_alias; END;
$BODY$; ALTER FUNCTION "Dcep".jsontechnologies() OWNER TO postgresql;

Well, you SELECT returns a single column of type JSON, but the function is declared to return multiple columns. Note that `array_to_json(array_agg(row_to_json(tech_alias)))` can be simplified to `json_agg(row_to_json(tech_alias))` — , Mar 13 '23 at 12:53
I changed it and checked , but the same error comes which i mentioned earlier. — Nithesh Kumar, Mar 13 '23 at 13:16
RETURN QUERY select json_agg(row_to_json(tech_alias)) from (select * from "Dcep"."Technologies") tech_alias; END; $BODY$; ALTER FUNCTION "Dcep".gettechnologiesjson() OWNER TO kumaran; — Nithesh Kumar, Mar 13 '23 at 13:18
Of course that doesn't make a difference. It only simplifies your existing query. But the query still doesn't match the table definition of your function. — , Mar 13 '23 at 13:38
Your function is declared to return 11 columns. Your query returns a single column (and a single row). — , Mar 13 '23 at 14:01
Can you please change the query and share me @a_horse_with_no_name since i am new to Postgresql. — Nithesh Kumar, Mar 13 '23 at 14:11
This is not about being new to Postgres, it is about thinking about what you are doing: *... getting table data in JSON format.* != `RETURNS TABLE` as per [Agg function](https://www.postgresql.org/docs/current/functions-aggregate.html) : *json_agg Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.)*. — Adrian Klaver, Mar 13 '23 at 15:05

score 0 · Accepted Answer · edited Mar 21 '23 at 11:32

0

I got my error resolved, changed my query to this:

select json_build_object('technologies', json_agg(techs)) technologies 
from (
select 
      t."Id",
      t."TechnologyName",
      t."TechnologyType", 
      t."Status", 
      t."ComponentID"
from "Dcep"."Technologies" t
)techs;

edited Mar 21 '23 at 11:32

F. Müller

3,969
8
38
49

answered Mar 15 '23 at 12:16

Nithesh Kumar

3
2

How to write a select function for converting table data to JSON?

1 Answers1