Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.postgresql > #667 > unrolled thread
| Started by | Robert Hartmann <Robert_Hartmann@gmx.net> |
|---|---|
| First post | 2015-06-02 23:21 +0200 |
| Last post | 2015-06-03 22:16 +0200 |
| Articles | 3 — 2 participants |
Back to article view | Back to comp.databases.postgresql
How to use PL/pgsql function returned varchar for prepared statement Robert Hartmann <Robert_Hartmann@gmx.net> - 2015-06-02 23:21 +0200
Re: How to use PL/pgsql function returned varchar for prepared statement gargoyle60 <gargoyle60@example.invalid> - 2015-06-03 08:55 +0100
Re: How to use PL/pgsql function returned varchar for prepared statement Robert Hartmann <Robert_Hartmann@gmx.net> - 2015-06-03 22:16 +0200
| From | Robert Hartmann <Robert_Hartmann@gmx.net> |
|---|---|
| Date | 2015-06-02 23:21 +0200 |
| Subject | How to use PL/pgsql function returned varchar for prepared statement |
| Message-ID | <mkl6m1$at6$1@speranza.aioe.org> |
Hi there, I have an PL/pgsql function based on http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ It returns a varchar containing an SQL-statement. Well with SELECT functionname(...); I get the generated SQLcode displayed. But how I can modify my SELECT-Statement so that the generated code is not only displayed but executed? Sadly there is no information (except on runtime) about how many colums should be generated. Or can I use the output from functionname for createing a prepared statement via PREPARE ? PREPARE cool() AS ***SELECT functionname(...); ***here something must be changed, but I have no idea. Best regards, Robert
[toc] | [next] | [standalone]
| From | gargoyle60 <gargoyle60@example.invalid> |
|---|---|
| Date | 2015-06-03 08:55 +0100 |
| Message-ID | <jmctmad3h9f3ritl8qivevaqg5j5cftcgf@4ax.com> |
| In reply to | #667 |
On Tue, 02 Jun 2015 23:21:07 +0200, Robert Hartmann <Robert_Hartmann@gmx.net> wrote: >But how I can modify my SELECT-Statement so that >the generated code is not only displayed but executed? Have you considered using DBLINK ? You could execute the returned SQL code and find a way to display it first.
[toc] | [prev] | [next] | [standalone]
| From | Robert Hartmann <Robert_Hartmann@gmx.net> |
|---|---|
| Date | 2015-06-03 22:16 +0200 |
| Message-ID | <mknn9g$djn$1@speranza.aioe.org> |
| In reply to | #668 |
Hi,
Am 03.06.2015 um 09:55 schrieb gargoyle60:
> On Tue, 02 Jun 2015 23:21:07 +0200, Robert Hartmann <Robert_Hartmann@gmx.net> wrote:
>
>> But how I can modify my SELECT-Statement so that
>> the generated code is not only displayed but executed?
>
> Have you considered using DBLINK ?
Well, no I havn't.
> You could execute the returned SQL code and find a way to display it first.
>
I am now found a way, to do it (but I don't like it that way).
Inside the same sql-script-file I am now writing:
********************
CREATE OR REPLACE FUNCTION rating_overview_horizontal()
RETURNS varchar
AS
$$
declare
dynamicSQLstring varchar;
BEGIN
SELECT
mypivotcode('rating_overview_temp','user_Info','task_title','Task_Solution_Info','varchar')
into dynamicSQLstring;
EXECUTE 'PREPARE do_something AS '|| dynamicSQLstring;
return dynamicSQLstring;
END;
$$
LANGUAGE plpgsql;
SELECT rating_overview_horizontal();
EXECUTE do_something;
DEALLOCATE PREPARE do_something;
********************
Best regards,
Robert
[toc] | [prev] | [standalone]
Back to top | Article view | comp.databases.postgresql
csiph-web