Tag Info

New answers tagged

0

As simple answer to the question, yes, it is possible to do this with a function. PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily. The solution is very brute-force and inelegant, and I wouldn't ...


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


3

Deprecated functions / features work fine in your current SQL Server Version. It is just a hint for you that in future versions of the SQL Server these functions will be dissmissed or replaced by others. It doesn't mean that these functions are removed in the very next version, but it is planned in long term to remove them. A list of deprecated features is ...


5

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts. Secondarily, you can also EXECUTE a Scalar UDF. This syntax ...


2

I think the difference in permissions is because you can actually invoke scalar-valued user-defined functions with EXEC just like stored procedures (which I hadn't realized until I dug into SQL Server 2000 Books Online, where they introduced user-defined functions), yet you can't actually select from them as a table source. For example: DECLARE @date ...



Top 50 recent answers are included