From: | Hadley Willan <hadley(dot)willan(at)deeperdesign(dot)co(dot)nz> |
---|---|
To: | PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Dynamic SQL |
Date: | 2004-06-02 06:29:20 |
Message-ID: | 1086157760.21815.107.camel@atlas.sol.deeper.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shridhar Daithankar | 2004-06-02 07:16:56 | Re: Insert speed question |
Previous Message | Tom Lane | 2004-06-01 23:35:32 | Re: async problems? |