Thread: RE : Re: Asking for some PL/pgSQL Tips

  Hello,

  I have tried to generate my dynamic query storeing the sql statement in a
variable, but still doesn't work...

  I don't know what's the matter with it, but when I  call the function:

     Select alterTable(20362, 'new_col', 'integer');

  It gives me the following mssg:

      ERROR: parser: parse error at or near "execute"

  I wonder if anyone can have a look at it and tell me what's wrong with it.
thankyou in advance.

                                    Stay Safe & Happy
                                             :* TankGirl

  P.S. The function:


CREATE FUNCTION recorrerAnchura(oid, TEXT, TEXT) RETURNS integer AS '
 DECLARE
      -- Alias for the parameters
      nomb_atrib ALIAS FOR $2;
      nomb_tipo ALIAS FOR $3;

      tuplas RECORD;
      tabla_origen text;

      -- Variable in charge of the dinamic query
      exec_sql varchar(4000);

 BEGIN

      -- I check if the oid belongs to a table
      tabla_origen := oidToText($1);
      IF tabla_origen = NULL THEN
            RAISE EXCEPTION '' The oid  % doesn't belong to any table!!!'', $1;
      ELSE
        -- I check if the table is inherited
        SELECT INTO tuplas * FROM pg_inherits WHERE inhparent = $1;
        IF NOT FOUND THEN
         -- This is the dynamic query I want to generate
         exec_sql :=  '' ALTER TABLE ''''''''''
                      || tabla_origen
                      || '''''''''' ADD COLUMN ''''''''''
                      || nomb_atrib ||'''''''''' ''''''''''
                      || nomb_tipo ||'''''''''';'';

        END IF;
      END IF;

      RAISE NOTICE '' %'', exec_sql;
      EXECUTE exec_sql;

      RETURN 0;
END;
 ' LANGUAGE 'plpgsql';



On Tue Jul 31 20:15:22 CEST 2001
Richard Huxton (dev@archonet.com)  wrote :

tankgirl@worldonline.es wrote:

>   This is more or less what I'm trying to do...
>
>             EXECUTE ''ALTER TABLE ''||name_table
>                     || '' ADD COLUMN '' || name_atrib || name_type;

Stick the text of this into a variable (say execsql) then you can do:

RAISE NOTICE execsql;
EXECUTE execsql;

Once I've got my patch to RAISE finished and accepted you won't need to
put it into a variable, but for the moment you have to.

My guess is you are missing a space between name_atrib and name_type,
but that's just a guess.

HTH

- Richard Huxton

----- Original Message -----
From: <tankgirl@worldonline.es>
To: <pgsql-general@postgresql.org>
Cc: <dev@archonet.com>
Sent: Thursday, August 02, 2001 8:32 AM
Subject: RE : Re: [GENERAL] Asking for some PL/pgSQL Tips


>   Hello,
>
>   I have tried to generate my dynamic query storeing the sql statement in
a
> variable, but still doesn't work...
>
>   I don't know what's the matter with it, but when I  call the function:
>
> Select alterTable(20362, 'new_col', 'integer');
>
>   It gives me the following mssg:
>
>   ERROR: parser: parse error at or near "execute"

Tweaked your code slightly (see below) and it works OK here:

richardh=> \d foo
          Table "foo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 a         | integer |
 foocol    | integer |

richardh=> select oid,relname from pg_class where relname='foo';
   oid   | relname
---------+---------
 2825890 | foo
(1 row)

richardh=> \i tankgirl0.txt
DROP
CREATE
richardh=> select recorrerAnchura(2825890,'foocol2','integer');
NOTICE:    ALTER TABLE foo ADD COLUMN foocol2 integer;
 recorreranchura
-----------------
               0
(1 row)

richardh=> \d foo
          Table "foo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 a         | integer |
 foocol    | integer |
 foocol2   | integer |


richardh=> select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96
(1 row)


And here is the altered function
DROP FUNCTION recorrerAnchura(oid,TEXT,TEXT);

CREATE FUNCTION recorrerAnchura(oid, TEXT, TEXT) RETURNS integer AS '
 DECLARE
      -- Alias for the parameters
      nomb_atrib ALIAS FOR $2;
      nomb_tipo ALIAS FOR $3;

      tuplas RECORD;
      tabla_origen text;

      -- Variable in charge of the dinamic query
      exec_sql varchar(4000);

 BEGIN
      -- I dont have oidtotext() so I cheat...
      -- tabla_origen := oidToText($1);
      tabla_origen := ''foo'';
      IF tabla_origen = NULL THEN
            RAISE EXCEPTION '' The oid  % does not belong to any table!!!'',
$1;
      ELSE
        -- I check if the table is inherited
        SELECT INTO tuplas * FROM pg_inherits WHERE inhparent = $1;
        IF NOT FOUND THEN
         -- This is the dynamic query I want to generate
        exec_sql :=  '' ALTER TABLE ''
                      || tabla_origen
                      || '' ADD COLUMN ''
                      || nomb_atrib ||'' ''
                      || nomb_tipo ||'';'';

        END IF;
      END IF;

      RAISE NOTICE '' %'', exec_sql;
      EXECUTE exec_sql;

      RETURN 0;
END;
 ' LANGUAGE 'plpgsql';

The changes are basically with the quoting for "exec_sql".

HTH

- Richard Huxton

tankgirl@worldonline.es writes:
>   It gives me the following mssg:
>       ERROR: parser: parse error at or near "execute"

I believe EXECUTE is new in 7.1.  What version are you running?

            regards, tom lane