Обсуждение: Like to RETURN SQL results set

Поиск
Список
Период
Сортировка

Like to RETURN SQL results set

От
M Sarwar
Дата:
 

 

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

 

Re: Like to RETURN SQL results set

От
Laurenz Albe
Дата:
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



RE: Like to RETURN SQL results set

От
M Sarwar
Дата:
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

Re: Like to RETURN SQL results set

От
Scott Ribe
Дата:
> 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.


Re: Like to RETURN SQL results set

От
M Sarwar
Дата:
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
 
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

Re: Like to RETURN SQL results set

От
Erik Wienhold
Дата:
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



Re: Like to RETURN SQL results set

От
M Sarwar
Дата:
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
 
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

Re: Like to RETURN SQL results set

От
Scott Ribe
Дата:
> 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;'





Re: Like to RETURN SQL results set

От
M Sarwar
Дата:
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
 
> 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;'