From: | tankgirl(at)worldonline(dot)es |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | dev(at)archonet(dot)com |
Subject: | RE : Re: Asking for some PL/pgSQL Tips |
Date: | 2001-08-02 07:32:22 |
Message-ID: | 4424898.996737542922.JavaMail.root@macaca.worldonline.es |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)archonet(dot)com) wrote :
tankgirl(at)worldonline(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | tankgirl | 2001-08-02 07:45:36 | RE : Re: Asking for some PL/pgSQL Tips |
Previous Message | Nils O. =?iso-8859-1?q?Sel=E5sdal?= | 2001-08-02 03:00:38 | RE: Finding number of current connections |