From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | <tankgirl(at)worldonline(dot)es>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Re: Asking for some PL/pgSQL Tips |
Date: | 2001-08-02 08:56:02 |
Message-ID: | 012101c11b30$fb51b260$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
----- Original Message -----
From: <tankgirl(at)worldonline(dot)es>
To: <pgsql-general(at)postgresql(dot)org>
Cc: <dev(at)archonet(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | tankgirl | 2001-08-02 09:52:15 | RE : Re: Re: Asking for some PL/pgSQL Tips |
Previous Message | tankgirl | 2001-08-02 07:45:36 | RE : Re: Asking for some PL/pgSQL Tips |