EXECUTE … USING only works in PL/PgSQL – ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL’s SQL dialect. Compare: PL/PgSQL’s EXECUTE … USING; to SQL’s … Read more
There is nothing wrong with a plpgsql function for anything a little more complex. The only situation where performance can suffer is when a plpgsql function is nested, because the query planner cannot further optimize the contained code in the context of the outer query which may or may not make it slower. More details … Read more
On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don’t need to update the values in the table itself. So from the code you posted you could use this approach for @TSku but not for @RelPro Example … Read more
The problem is with implicit conversion. If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to nVarChar(4000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter! When concatenating long strings … Read more
DECLARE @sqlCommand NVARCHAR(1000) DECLARE @count INT DECLARE @city VARCHAR(75) SET @city = ‘New York’ SET @sqlCommand = ‘SELECT @cnt=COUNT(*) FROM customers WHERE City = @city’ EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75), @cnt int OUTPUT’, @city = @city, @cnt = @count OUTPUT SELECT @count
You must defend against SQL injection whenever you turn user input into code. That includes table and column names coming from system catalogs or from direct user input alike. This way you also prevent trivial exceptions with non-standard identifiers. There are basically three built-in methods: 1. format() 1st query, sanitized: CREATE OR REPLACE FUNCTION foo(_t … Read more
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here’s the script: CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname=”public”; BEGIN FOR stmt IN statements LOOP EXECUTE ‘TRUNCATE TABLE ‘ || quote_ident(stmt.tablename) || ‘ CASCADE;’; END LOOP; END; $$ LANGUAGE … Read more
If the list of conditions is not known at compile time and is instead built at run time, you don’t have to worry about whether you have one or more than one condition. You can generate them all like: and <condition> and concatenate them all together. With the 1=1 at the start, the initial and … Read more
Modern PostgreSQL format() has a built-in way to escape identifiers. Simpler than before: CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘INSERT INTO %I.%I SELECT $1.*’ , TG_TABLE_SCHEMA, TG_TABLE_NAME || ‘shadow’) USING OLD; RETURN OLD; END $func$; Works with a VALUES expression as well. db<>fiddle here Old sqlfiddle Major points … Read more
If you have OUTPUT parameters you can do DECLARE @retval int DECLARE @sSQL nvarchar(500); DECLARE @ParmDefinition nvarchar(500); DECLARE @tablename nvarchar(50) SELECT @tablename = N’products’ SELECT @sSQL = N’SELECT @retvalOUT = MAX(ID) FROM ‘ + @tablename; SET @ParmDefinition = N’@retvalOUT int OUTPUT’; EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; SELECT @retval; But if you don’t, and can … Read more