Thread: 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@gmail.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,

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.tyrtania@byte-employer.de

Thanks for the reply,

It is working now.

Best Regards.