Dynamic SQL with pgsql, how to?

Hi,

I need to write some dynamic SQL in pgsql.

I have to do something like this:

[code=SQL Server]
SET @STRINGN = @STRINGN + ' AND A.' + @CAMPOFECINI + ' IN (SELECT
MAX(B.' + @CAMPOFECINI + ')
FROM ' + @TABLA + ' B
WHERE B.ID_SOCIEDAD = A.ID_SOCIEDAD
AND B.ID_EMPREGAD = A.ID_EMPREGAD'
IF @F_ALTA IS NOT NULL
SET @STRINGN = @STRINGN + ' AND B.DAT_INI_ACT_EMP =
A.DAT_INI_ACT_EMP'
SET @STRINGN = @STRINGN + ')'
EXEC sp_executesql @STRINGN,
N'@FINI2 datetime out, @FFIN2 datetime out, @CAMP2
varchar(50) out',
@FINI out, @FFIN out , @CAMP out
[/code]

There is documentation on how can I do this in pgsql?

Best Regards,

On Mon, May 3, 2010 at 10:44 AM, Andre Lopes <lopes80andre(at)gmail(dot)com> wrote:
> Hi,
>
> I need to write some dynamic SQL in pgsql.
>
> I have to do something like this:
>
> [code=SQL Server]
>     SET @STRINGN = @STRINGN + ' AND A.' + @CAMPOFECINI + ' IN (SELECT
> MAX(B.' + @CAMPOFECINI + ')
>             FROM ' + @TABLA + ' B
>             WHERE B.ID_SOCIEDAD = A.ID_SOCIEDAD
>             AND B.ID_EMPREGAD = A.ID_EMPREGAD'
>     IF @F_ALTA IS NOT NULL
>         SET @STRINGN = @STRINGN + ' AND B.DAT_INI_ACT_EMP =
> A.DAT_INI_ACT_EMP'
>     SET @STRINGN = @STRINGN + ')'
>        EXEC sp_executesql @STRINGN,
>                 N'@FINI2 datetime out, @FFIN2 datetime out, @CAMP2
> varchar(50) out',
>                 @FINI out, @FFIN out , @CAMP out
> [/code]
>
> There is documentation on how can I do this in pgsql?
>
> Best Regards,
>

Hi Andre,

This may helps

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best regards,
Jorge

In response to Andre Lopes :
> Hi,
>
> I need to write some dynamic SQL in pgsql.
> There is documentation on how can I do this in pgsql?

Sure,
http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

Thanks for the reply's,

I need to do a Dynamic SELECT INTO. There is a way of doing it?

Best Regards,

On Mon, May 3, 2010 at 10:05 AM, A. Kretschmer <
andreas(dot)kretschmer(at)schollglas(dot)com> wrote:

> In response to Andre Lopes :
> > Hi,
> >
> > I need to write some dynamic SQL in pgsql.
> > There is documentation on how can I do this in pgsql?
>
> Sure,
>
> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Am 03.05.2010 um 23:50 schrieb Andre Lopes:

> Thanks for the reply's,
>
> I need to do a Dynamic SELECT INTO. There is a way of doing it?

Yes. Plpgsql supports this:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

See http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

Max

Maximilian Tyrtania Software-Entwicklung
Dessauer Str. 6-7
10969 Berlin
Tel.: ++49/30/48827-952
Mobil: 0152/292 707 36
email: maximilian(dot)tyrtania(at)byte-employer(dot)de

Thanks for the reply,

It is working now.

Best Regards.

On Tue, May 4, 2010 at 7:44 AM, Maximilian Tyrtania <
maximilian(dot)tyrtania(at)byte-employer(dot)de> wrote:

> Am 03.05.2010 um 23:50 schrieb Andre Lopes:
>
> > Thanks for the reply's,
> >
> > I need to do a Dynamic SELECT INTO. There is a way of doing it?
>
> Yes. Plpgsql supports this:
>
> EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ]
> ];
>
> See
> http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html
>
> Max
>
> Maximilian Tyrtania Software-Entwicklung
> Dessauer Str. 6-7
> 10969 Berlin
> Tel.: ++49/30/48827-952
> Mobil: 0152/292 707 36
> email: maximilian(dot)tyrtania(at)byte-employer(dot)de