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