Tag Info

New answers tagged

0

Yes, this should work (untested): CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan() -- generic name RETURNS trigger AS $func$ DECLARE _timetype varchar; _timetypespan_resume interval; _ct int; BEGIN CASE NEW.timetype WHEN 'lap' THEN EXECUTE format($$ SELECT timetype, timetypespan, age($1, timestmp) FROM %s WHERE ...


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


1

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation: TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. ...


2

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql: execute format('SELECT columnname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements. The query above is ...


2

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want: BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END; or BEGIN EXECUTE format('SELECT colname FROM %I.%I', ...


2

You can avoid various complications by passing values as values with the USING clause: CREATE OR REPLACE FUNCTION foo(linktable regclass, inttable regclass, verttable regclass) RETURNS void AS $func$ BEGIN EXECUTE format( 'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t' ...


1

While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead: FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i]; END LOOP; ...


3

My own solution so far is to paste the string literal into the query: EXECUTE format( 'UPDATE %I SET %I = ' || quote_literal(newvalue) || ' WHERE %I = $1 ', relname, colname, relname || '_id') USING row_id; or just EXECUTE format( 'UPDATE %I SET %I = %L WHERE %I = $1', relname, colname, newvalue, relname || '_id') USING row_id; This works for, ...



Top 50 recent answers are included