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


0

It sounds like a task for the presentation layer and not something that should not be done using SQL Server... Having said that, you can use an undocumented side-effect like this: DECLARE @MyConcatString VARCHAR(MAX) = '' SELECT @MyConcatString = @MyConcatString + name+ ',' FROM sys.objects PRINT @MyConcatString To concatenate the name from all rows in ...


2

Use FOR XML PATH Sample from TSQL – Concatenate Rows using FOR XML PATH() - sqlandme.com SELECT CAT.Name AS [Category], STUFF(( SELECT ',' + SUB.Name AS [text()] — Add a comma (,) before each value FROM Production.ProductSubcategory SUB WHERE ...


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


11

Just to get these out of the way: Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity ...


3

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary to retrieve a valid result set will be the path chosen. SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1); The server will always return all of the rows, ...



Top 50 recent answers are included