[GENERAL] 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