You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more
I believe there are three different cases that you have to worry about: strings (anything that requires quotes): ”” + replace(@string, ””, ”””) + ”” names (anything where quotes aren’t allowed): quotename(@string) things that cannot be quoted: this requires whitelisting Note: Everything in a string variable (char, varchar, nchar, nvarchar, etc.) that comes from user-controlled … Read more
This would be shorter, faster and safer: CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass , OUT parent_ltree ltree) LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘SELECT l_tree FROM %s WHERE id = $1′, tbl_name) INTO parent_ltree USING parent_id; END $func$; Why? Most importantly, use the USING clause of EXECUTE for parameter values. Don’t convert them … Read more
Dynamic SQL is merely where the query has been built on the fly – with some vendors, you can build up the text of the dynamic query within one stored procedure, and then execute the generated SQL. In other cases, the term merely refers to a decision made by code on the client (this is … Read more
Simple script (question asked) Building on @a_horse_with_no_name‘s answer and improved with @Gregory’s comment: DO $do$ BEGIN IF EXISTS ( SELECT FROM pg_catalog.pg_roles WHERE rolname=”my_user”) THEN RAISE NOTICE ‘Role “my_user” already exists. Skipping.’; ELSE CREATE ROLE my_user LOGIN PASSWORD ‘my_password’; END IF; END $do$; Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS … Read more
A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors. Declare @UserID varchar(100) declare @sqlstatement nvarchar(4000) –move declare cursor into sql to be … Read more
You need dynamic SQL: CREATE OR REPLACE FUNCTION loaddata(filepathname text) RETURNS void AS $func$ BEGIN EXECUTE format (‘ COPY climatedata( climatestationid , date … — more columns , tminsflag) FROM %L (FORMAT CSV, HEADER)’ — current syntax — WITH CSV HEADER’ — tolerated legacy syntax , $1); — pass function parameter filepathname to format() END … Read more
CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup() RETURNS TABLE(natural_id text, name text, natural_id_numeric text) LANGUAGE plpgsql AS $func$ DECLARE formal_table text; BEGIN FOR formal_table IN SELECT quote_ident(table_name) FROM information_schema.tables WHERE table_schema=”public” AND table_name LIKE ‘formaltable%’ LOOP RETURN QUERY EXECUTE ‘SELECT t.natural_id, t.name, t.natural_id_numeric FROM internal_idlookup i JOIN public.’ || formal_table || ‘ t USING (natural_id_numeric) WHERE i.internal_id … Read more
You may need to modify the query to include the owner if there’s more than one in the database. DECLARE @cmd varchar(4000) DECLARE cmds CURSOR FOR SELECT ‘drop table [‘ + Table_Name + ‘]’ FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘prefix%’ OPEN cmds WHILE 1 = 1 BEGIN FETCH cmds INTO @cmd IF @@fetch_status != 0 … Read more
Answer DO expects a string literal with plpgsql code. Symbols are not substituted inside strings in psql. You could concatenate the whole string into a psql variable and then execute it. How to concatenate psql variables? Pretty multi-line format is not possible, because (per documentation): But in any case, the arguments of a meta-command cannot … Read more