How to use EXECUTE FORMAT ... USING in postgres function
Your function could look like this in Postgres 9.0 or later:
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
RETURNS trigger AS
$func$
DECLARE
v_partition_name text := quote_ident('dummyTest'); -- assign at declaration
BEGIN
IF NEW.datetime IS NOT NULL THEN
EXECUTE
'INSERT INTO ' || v_partition_name || ' VALUES ($1,$2)'
USING NEW.id, NEW.datetime;
END IF;
RETURN NULL; -- You sure about this?
END
$func$ LANGUAGE plpgsql;
About RETURN NULL
:
- To ignore result in BEFORE TRIGGER of PostgreSQL?
I would advice not to use mixed case identifiers. With format( .. %I ..)
or quote_ident()
, you'd get a table named "dummyTest"
, which you'll have to double quote for the rest of its existence. Related:
- Are PostgreSQL column names case-sensitive?
Use lower case instead:
quote_ident('dummytest')
There is really no point in using dynamic SQL with EXECUTE
as long as you have a static table name. But that's probably just the simplified example?
You need explicit cast to text
:
EXECUTE format('INSERT INTO %I VALUES ($1,$2)'::text ,v_partition_name) using NEW.id,NEW.datetime;