Re: Re: Asking for some PL/pgSQL Tips

----- 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