Postgres Pro
Downloads
Home   >   mailing lists

Thread: : Execute a string of command in sql'script

: Execute a string of command in sql'script

From
"pblunat"
Date:
Hi all,
I am so sorry but problem below is very urgent for me now!!!
If isn't possible to do, i must take another way of developpement for my
data base.

I'm using PostgreSQL 7.0.2 on RedHat 6.0.

 I compose a SQL command string with function below :


 CREATE FUNCTION bdrm_copy(text, text) RETURNS text AS '

  DECLARE
   baseact  ALIAS FOR $1;
   namefich ALIAS FOR $2;
   path0 TEXT;
   path1 TEXT;
   path2 TEXT;
   path3 TEXT;
  BEGIN
   SELECT INTO path0 CAST(ig_tempo AS TEXT)
   FROM ti_bdrm
   WHERE ig_base = baseact;
   path0  := trim(path0) || trim(namefich);
   path1 := ''COPY ttx_window7 TO \'\''';
   path2 := path1 || path0;
   path3 := ''\'\''';
   path0  := path2 || path3 || '';'';
   RETURN path0 ;
  END;'
 LANGUAGE 'plpgsql';


 the result of :

 SELECT bdrm_copy('MOHO99', 'test2.txt') AS my_command_string;

 is :

                my_command_string
 -----------------------------------------------
  COPY ttx_window7 TO '/db/bdrm/out/test2.txt';


 Is it possible to execute this line of command? More generally, can one
 execute the contents of a variable or a field in postgresql' script?

Thanks ;)-

RE: : Execute a string of command in sql'script

From
"Henshall, Stuart - WCP"
Date:
    Could you execute aa system command (perhaps in a c function using
system) to pass this command to psql. (maybe write it to a file first then
redirect stdin, or perhaps pipe an echo command if using shell to exec
psql.) (ok so theres got to be a better way, but it might work :) )
- Stuart

> -----Original Message-----
> From:    pblunat [SMTP:pblunat@ujf-grenoble.fr]
> Sent:    Monday, January 29, 2001 12:30 PM
> To:    pgsql-general@postgresql.org
> Subject:    : Execute a string of command in sql'script
>
> Hi all,
> I am so sorry but problem below is very urgent for me now!!!
> If isn't possible to do, i must take another way of developpement for my
> data base.
>
> I'm using PostgreSQL 7.0.2 on RedHat 6.0.
>
>  I compose a SQL command string with function below :
>
>
>  CREATE FUNCTION bdrm_copy(text, text) RETURNS text AS '
>
>   DECLARE
>    baseact  ALIAS FOR $1;
>    namefich ALIAS FOR $2;
>    path0 TEXT;
>    path1 TEXT;
>    path2 TEXT;
>    path3 TEXT;
>   BEGIN
>    SELECT INTO path0 CAST(ig_tempo AS TEXT)
>    FROM ti_bdrm
>    WHERE ig_base = baseact;
>    path0  := trim(path0) || trim(namefich);
>    path1 := ''COPY ttx_window7 TO \'\''';
>    path2 := path1 || path0;
>    path3 := ''\'\''';
>    path0  := path2 || path3 || '';'';
>    RETURN path0 ;
>   END;'
>  LANGUAGE 'plpgsql';
>
>
>  the result of :
>
>  SELECT bdrm_copy('MOHO99', 'test2.txt') AS my_command_string;
>
>  is :
>
>                 my_command_string
>  -----------------------------------------------
>   COPY ttx_window7 TO '/db/bdrm/out/test2.txt';
>
>
>  Is it possible to execute this line of command? More generally, can one
>  execute the contents of a variable or a field in postgresql' script?
>
> Thanks ;)-
>
>
>
>

Re: : Execute a string of command in sql'script

From
Jan Wieck
Date:
pblunat wrote:
> Hi all,
> I am so sorry but problem below is very urgent for me now!!!
> If isn't possible to do, i must take another way of developpement for my
> data base.
>
> I'm using PostgreSQL 7.0.2 on RedHat 6.0.
>
>  I compose a SQL command string with function below :
>
>
>  CREATE FUNCTION bdrm_copy(text, text) RETURNS text AS '
>
>   DECLARE
>    baseact  ALIAS FOR $1;
>    namefich ALIAS FOR $2;
>    path0 TEXT;
>    path1 TEXT;
>    path2 TEXT;
>    path3 TEXT;
>   BEGIN
>    SELECT INTO path0 CAST(ig_tempo AS TEXT)
>    FROM ti_bdrm
>    WHERE ig_base = baseact;
>    path0  := trim(path0) || trim(namefich);
>    path1 := ''COPY ttx_window7 TO \'\''';
>    path2 := path1 || path0;
>    path3 := ''\'\''';
>    path0  := path2 || path3 || '';'';
>    RETURN path0 ;
>   END;'
>  LANGUAGE 'plpgsql';
>
>
>  the result of :
>
>  SELECT bdrm_copy('MOHO99', 'test2.txt') AS my_command_string;
>
>  is :
>
>                 my_command_string
>  -----------------------------------------------
>   COPY ttx_window7 TO '/db/bdrm/out/test2.txt';
>
>
>  Is it possible to execute this line of command? More generally, can one
>  execute the contents of a variable or a field in postgresql' script?

    It will be possible in 7.1. With current BETA4 you can do an

        EXECUTE path0;

    just before the RETURN and it'll do the job.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: : Execute a string of command in sql'script

From
Tom Lane
Date:
"pblunat" <pblunat@ujf-grenoble.fr> writes:
>  Is it possible to execute this line of command? More generally, can one
>  execute the contents of a variable or a field in postgresql' script?

Not in plpgsql (at least not till 7.1), but you could do it in pltcl
or plperl.

            regards, tom lane