From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Hadley Willan <hadley(dot)willan(at)deeperdesign(dot)co(dot)nz> |
Cc: | PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Dynamic SQL |
Date: | 2004-06-02 08:23:26 |
Message-ID: | 40BD8E7E.9080200@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hadley Willan wrote:
> Hi all,
> I'd like to write a function that would allow me to verify some
> attributes on a table so that I could give a meaningful error message...
>
> constructedSql = ''SELECT INTO recCheckObject * FROM '' ||
> tableName || '' WHERE '' || idColumn || '' = '' || objectId;
> EXECUTE constructedSql;
> However, when you try and run this it's like the SELECT INTO <record> is
> failing to pickup the reference to the declared variable?
Sounds likely, and (without testing anything) I wouldn't expect it to work.
Indeed, checking the manuals: plpgsql / basic statements (37.6.4)
"The results from SELECT commands are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE. There are two ways to
extract a result from a dynamically-created SELECT: one is to use the
FOR-IN-EXECUTE loop form described in Section 37.7.4, and the other is
to use a cursor with OPEN-FOR-EXECUTE, as described in Section 37.8.2."
There's your solution - you need to use the FOR rec IN ... looping
construct.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-06-02 08:28:52 | Re: Providing the password to psql from a script |
Previous Message | Richard Huxton | 2004-06-02 08:10:08 | Re: after using pg_resetxlog, db lost |