Обсуждение: Like to RETURN SQL results set
Hello,
I am writing a dynamic function in which number of columns to be retrieved change depending on the parameter values passed while invoking the function. I am using CROSSTAB in the dynamic SQL.
Once I prepare a dynamic SQL and I execute, I want the entire result set from the executed SQL to be returned to function RETURN.
Is this possible? If yes, can you please point me to some examples.
Thanks,
Sarwar
On Wed, 2024-03-27 at 20:40 +0000, M Sarwar wrote: > I am writing a dynamic function in which number of columns to be retrieved change > depending on the parameter values passed while invoking the function. > I am using CROSSTAB in the dynamic SQL. > Once I prepare a dynamic SQL and I execute, I want the entire result set from > the executed SQL to be returned to function RETURN. > Is this possible? If yes, can you please point me to some examples. No, this is not possible, unless you declare the function as "RETURNS SETOF record". But then you have to specify the column list when you use the function, e.g.: SELECT * FROM func() AS (c1 integer, c2 text) Yours, Laurenz Albe
Hi Laurenz,
I truly appreciate your response.
Sometimes, I am unable to confirm where I am heading.
Thanks,
Sarwar
Sent from my Galaxy
-------- Original message --------
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: 3/28/24 5:19 AM (GMT-05:00)
To: M Sarwar <sarwarmd02@outlook.com>, pgsql-admin@lists.postgresql.org
Subject: Re: Like to RETURN SQL results set
On Wed, 2024-03-27 at 20:40 +0000, M Sarwar wrote:
> I am writing a dynamic function in which number of columns to be retrieved change
> depending on the parameter values passed while invoking the function.
> I am using CROSSTAB in the dynamic SQL.
> Once I prepare a dynamic SQL and I execute, I want the entire result set from
> the executed SQL to be returned to function RETURN.
> Is this possible? If yes, can you please point me to some examples.
No, this is not possible, unless you declare the function as "RETURNS SETOF record".
But then you have to specify the column list when you use the function, e.g.:
SELECT * FROM func() AS (c1 integer, c2 text)
Yours,
Laurenz Albe
> I am writing a dynamic function in which number of columns to be retrieved change
> depending on the parameter values passed while invoking the function.
> I am using CROSSTAB in the dynamic SQL.
> Once I prepare a dynamic SQL and I execute, I want the entire result set from
> the executed SQL to be returned to function RETURN.
> Is this possible? If yes, can you please point me to some examples.
No, this is not possible, unless you declare the function as "RETURNS SETOF record".
But then you have to specify the column list when you use the function, e.g.:
SELECT * FROM func() AS (c1 integer, c2 text)
Yours,
Laurenz Albe
> On Mar 28, 2024, at 7:36 AM, M Sarwar <sarwarmd02@outlook.com> wrote: > > Sometimes, I am unable to confirm where I am heading. I think I may adopt that as my personal slogan for 2024.
Laurenz:
Numbers of returning columns will vary dynamically.
Based on that SELECT * FROM func() AS (c1 integer, c2 text) is not applicable in my case.
Thanks,
Sarwar
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, March 28, 2024 5:19 AM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
Sent: Thursday, March 28, 2024 5:19 AM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
On Wed, 2024-03-27 at 20:40 +0000, M Sarwar wrote:
> I am writing a dynamic function in which number of columns to be retrieved change
> depending on the parameter values passed while invoking the function.
> I am using CROSSTAB in the dynamic SQL.
> Once I prepare a dynamic SQL and I execute, I want the entire result set from
> the executed SQL to be returned to function RETURN.
> Is this possible? If yes, can you please point me to some examples.
No, this is not possible, unless you declare the function as "RETURNS SETOF record".
But then you have to specify the column list when you use the function, e.g.:
SELECT * FROM func() AS (c1 integer, c2 text)
Yours,
Laurenz Albe
> I am writing a dynamic function in which number of columns to be retrieved change
> depending on the parameter values passed while invoking the function.
> I am using CROSSTAB in the dynamic SQL.
> Once I prepare a dynamic SQL and I execute, I want the entire result set from
> the executed SQL to be returned to function RETURN.
> Is this possible? If yes, can you please point me to some examples.
No, this is not possible, unless you declare the function as "RETURNS SETOF record".
But then you have to specify the column list when you use the function, e.g.:
SELECT * FROM func() AS (c1 integer, c2 text)
Yours,
Laurenz Albe
On 2024-03-28 23:27 +0100, M Sarwar wrote: > Numbers of returning columns will vary dynamically. Based on that > SELECT * FROM func() AS (c1 integer, c2 text) is not applicable in my > case. Dynamic table structures are hard to do in SQL, one of the reasons why document databases and the NoSQL movement exist. So why use crosstab when it doesn't fit your use case? Can you achieve the same with a view or function that returns jsonb like it has been suggested[1] to you before? Does the query even have to be implemented inside the database? [1] https://www.postgresql.org/message-id/CAJJx%2BiUFyTAN9F-C7xBx0sunr08aPvF_EnkGiHm7SP2mhakpqQ%40mail.gmail.com -- Erik
Hi Erik,
I see this posting first time. I did not receive this email or link in the past.
I have read the entire link. I appreciate looking into this issue.
At this time, I do not have option of going to the document database or NoSQL.
I have developed the function to cater our needs. In our case, view might not work out.
I am getting a SQL which is generated dynamically. I will be happy to share in case anyone is interested.
Now I need to execute the SQL dynamically which is generated dynamically. I am facing challenges in the executing the SQL.
I could not follow JSON part. Do you have any case where I can see JSON is used to solve this type of problem.
This may have solutions from many tools but we are staying with pgsql because currently it is installed on aws/rds /postgres. W do not need to worry about any licenses.
Thanks,
Sarwar
From: Erik Wienhold <ewie@ewie.name>
Sent: Thursday, March 28, 2024 6:46 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
Sent: Thursday, March 28, 2024 6:46 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
On 2024-03-28 23:27 +0100, M Sarwar wrote:
> Numbers of returning columns will vary dynamically. Based on that
> SELECT * FROM func() AS (c1 integer, c2 text) is not applicable in my
> case.
Dynamic table structures are hard to do in SQL, one of the reasons why
document databases and the NoSQL movement exist. So why use crosstab
when it doesn't fit your use case? Can you achieve the same with a view
or function that returns jsonb like it has been suggested[1] to you
before? Does the query even have to be implemented inside the database?
[1] https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2FCAJJx%252BiUFyTAN9F-C7xBx0sunr08aPvF_EnkGiHm7SP2mhakpqQ%2540mail.gmail.com&data=05%7C02%7C%7Ce9dc3ed5735f44b7379908dc4f78dd1b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638472627726303862%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=tb%2BWrL7fsm%2FWdEhWy5jGuORewJ8jBvQXeJ%2Fx670BSts%3D&reserved=0
--
Erik
> Numbers of returning columns will vary dynamically. Based on that
> SELECT * FROM func() AS (c1 integer, c2 text) is not applicable in my
> case.
Dynamic table structures are hard to do in SQL, one of the reasons why
document databases and the NoSQL movement exist. So why use crosstab
when it doesn't fit your use case? Can you achieve the same with a view
or function that returns jsonb like it has been suggested[1] to you
before? Does the query even have to be implemented inside the database?
[1] https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2FCAJJx%252BiUFyTAN9F-C7xBx0sunr08aPvF_EnkGiHm7SP2mhakpqQ%2540mail.gmail.com&data=05%7C02%7C%7Ce9dc3ed5735f44b7379908dc4f78dd1b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638472627726303862%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=tb%2BWrL7fsm%2FWdEhWy5jGuORewJ8jBvQXeJ%2Fx670BSts%3D&reserved=0
--
Erik
> On Mar 28, 2024, at 9:39 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> I could not follow JSON part. Do you have any case where I can see JSON is used to solve this type of problem.
To get json back, take any query and wrap it in
'select array_to_json(array_agg(row_to_json(t)))::text from (' || query || ') as t;'
Scott,
I need to work on this to explore this option.
Thanks a lot!
Sarwar
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Thursday, March 28, 2024 11:54 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
Sent: Thursday, March 28, 2024 11:54 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Like to RETURN SQL results set
> On Mar 28, 2024, at 9:39 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> I could not follow JSON part. Do you have any case where I can see JSON is used to solve this type of problem.
To get json back, take any query and wrap it in
'select array_to_json(array_agg(row_to_json(t)))::text from (' || query || ') as t;'
>
> I could not follow JSON part. Do you have any case where I can see JSON is used to solve this type of problem.
To get json back, take any query and wrap it in
'select array_to_json(array_agg(row_to_json(t)))::text from (' || query || ') as t;'