Dynamic SQL

[Hadley Willan]

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

I've been playing with passing in to a plpgsql function the following
things.

CREATE OR REPLACE FUNCTION fn_verifyObject( VARCHAR, VARCHAR, BIGINT,
VARCHAR ) RETURNS BOOLEAN AS'
declare
tableName ALIAS FOR $1;
idColumn ALIAS FOR $2;
objectId ALIAS FOR $3;
errorMsg ALIAS FOR $4;

recCheckObject RECORD;
constructedSql TEXT := '''';
begin
constructedSql = ''SELECT INTO recCheckObject * FROM '' ||
tableName || '' WHERE '' || idColumn || '' = '' || objectId;
EXECUTE constructedSql;

-- this is where I expect the command to run, as though I had typed it
in and thus populate, or fail to populate the record recCheckObject.
IF NOT FOUND THEN
RAISE EXCEPTION ''VERIFY OBJECT FAILED FOR:%:%:%:%'',
tableName, idColumn, objectId, errorMsg;
END IF;

--found something, therefore success.
RETURN TRUE;
END;'language'plpgsql';

CREATE TABLE testcode(
id BIGINT NOT NULL,
CONSTRAINT pk_testcode PRIMARY KEY( id )
);

INSERT INTO testcode VALUES ( 1 );

SELECT fn_verifyObject( 'testcode', 'id', 1, 'VERFIY_TEST_CODE' );
However, when you try and run this it's like the SELECT INTO <record> is
failing to pickup the reference to the declared variable?

I get...ERROR: syntax error at or near "INTO" at character 8
CONTEXT: PL/pgSQL function "fn_verifyobject" line 11 at execute
statement

Can anybody help by telling me how to either quote or execute this
constructed SQL correctly.

Regards.
Hadley

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings