Thread: Pass parameters to SQL script

Hi,

is it possible to pass parameters to a SQL script, which I launch via the psql shell?

In Oracle it works like that:

        sqlplus myscript.sql myschema

and within the script I can use the parameter like this:
       
        CONCAT .
        CREATE TABLE &1..test (name VARCHAR2(100));

Is there a counterpart for PostgreSQL?

thanks,
Clemens

Hi,

Citing Fuchs Clemens <clemens.fuchs@siemens.com>:
> is it possible to pass parameters to a SQL script, which I launch via the
> psql shell?

yes

> In Oracle it works like that:
>
>     sqlplus myscript.sql myschema
>
> and within the script I can use the parameter like this:
>
>     CONCAT .
>     CREATE TABLE &1..test (name VARCHAR2(100));
>
> Is there a counterpart for PostgreSQL?

call psql like this to set a variable named your_variable_name to my_table:

psql -v your_variable_name=my_table

to expand the variable your_variable_name to its value (my_table in this case)
in the sql script, precede its name with a colon, like so:

select * from :your_variable_name;

which will expand to:

select * from my_table;

the psql manpage has more info on all this.

Regards,
Daniel

thanks for tip - I'm nearly happy now.

Now I want to concatenate a variable value with a "hardcoded" value in my script - something like:

CREATE TABLE :myValue + _the_hardcoded_string ......

Is this possible?

thanks,
Clemens

-----Ursprüngliche Nachricht-----
Von: Daniel Martini [mailto:dmartini@uni-hohenheim.de]
Gesendet: Donnerstag, 19. August 2004 12:00
An: Fuchs Clemens
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Pass parameters to SQL script

Hi,

Citing Fuchs Clemens <clemens.fuchs@siemens.com>:
> is it possible to pass parameters to a SQL script, which I launch via the
> psql shell?

yes

> In Oracle it works like that:
>
>       sqlplus myscript.sql myschema
>
> and within the script I can use the parameter like this:
>      
>       CONCAT .
>       CREATE TABLE &1..test (name VARCHAR2(100));
>
> Is there a counterpart for PostgreSQL?

call psql like this to set a variable named your_variable_name to my_table:

psql -v your_variable_name=my_table

to expand the variable your_variable_name to its value (my_table in this case)
in the sql script, precede its name with a colon, like so:

select * from :your_variable_name;

which will expand to:

select * from my_table;

the psql manpage has more info on all this.

Regards,
Daniel

Am Donnerstag, 19. August 2004 15:39 schrieb Fuchs Clemens:
> Now I want to concatenate a variable value with a "hardcoded" value in my
> script - something like:
>
> CREATE TABLE :myValue + _the_hardcoded_string ......

Option 1:

\set tmp :myValue 'hardcoded'
CREATE TABLE :tmp ...

Option 2:

CREATE TABLE :myValue
"hardcoded" ...

If myValue is double quoted, then the values will automatically be
concatenated by the backend parser, but for strange (SQL-standard) reasons
you need a line break in between.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> If myValue is double quoted, then the values will automatically be
> concatenated by the backend parser, but for strange (SQL-standard) reasons
> you need a line break in between.

That works for literals (single-quotes), but I don't think it applies
to identifiers (double-quotes).

            regards, tom lane