pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLike to RETURN SQL results set
9+ messages / 4 participants
[nested] [flat]
* Like to RETURN SQL results set
@ 2024-03-27 20:40 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 9+ messages in thread
From: M Sarwar @ 2024-03-27 20:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-28 09:19 Laurenz Albe <laurenz.albe@cybertec.at>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 2 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-03-28 09:19 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Like to RETURN SQL results set
@ 2024-03-28 13:36 M Sarwar <sarwarmd02@outlook.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 9+ messages in thread
From: M Sarwar @ 2024-03-28 13:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-28 13:47 Scott Ribe <scott_ribe@elevated-dev.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2024-03-28 13:47 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-28 22:27 M Sarwar <sarwarmd02@outlook.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 9+ messages in thread
From: M Sarwar @ 2024-03-28 22:27 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-28 22:46 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 9+ messages in thread
From: Erik Wienhold @ 2024-03-28 22:46 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.g...
--
Erik
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-29 03:39 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 9+ messages in thread
From: M Sarwar @ 2024-03-29 03:39 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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%2FC...;
--
Erik
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-29 03:54 Scott Ribe <scott_ribe@elevated-dev.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2024-03-29 03:54 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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;'
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Like to RETURN SQL results set
@ 2024-03-29 03:57 M Sarwar <sarwarmd02@outlook.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 9+ messages in thread
From: M Sarwar @ 2024-03-29 03:57 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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;'
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-03-29 03:57 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-27 20:40 Like to RETURN SQL results set M Sarwar <sarwarmd02@outlook.com>
2024-03-28 09:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-28 13:36 ` M Sarwar <sarwarmd02@outlook.com>
2024-03-28 13:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-03-28 22:27 ` M Sarwar <sarwarmd02@outlook.com>
2024-03-28 22:46 ` Erik Wienhold <ewie@ewie.name>
2024-03-29 03:39 ` M Sarwar <sarwarmd02@outlook.com>
2024-03-29 03:54 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-03-29 03:57 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox