I encountered this error in liquibase and this page was one of the first search results so I guess I share my solution at this page: You can put your whole sql in a separate file and include this in the changeset. Its important to set the splitStatements option to false. The whole changeset would … Read more
Postgres 9.1 added FOREACH to loop over arrays: DO $do$ DECLARE _arr int[] := ‘{1,2,3,4}’; _elem int; — matching element type BEGIN FOREACH _elem IN ARRAY _arr LOOP RAISE NOTICE ‘%’, _elem; END LOOP; END $do$; db<>fiddle here Works for multi-dimensional arrays, too: Postgres flattens the array and iterates over all elements. To loop over … Read more
This is by design. The manual explains in the chapter Variable Substitution: Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the … Read more
To my knowledge there is no built-in function to replace empty strings across all columns of a table. You can write a plpgsql function to take care of that. The following function replaces empty strings in all basic character-type columns of a given table with NULL. You can then cast to integer if the remaining … Read more
I disagree with some of the advice in other answers. This can be done with PL/pgSQL and I think it is mostly far superior to assembling queries in a client application. It is faster and cleaner and the app only sends the bare minimum across the wire in requests. SQL statements are saved inside the … Read more
COPY does not allow variable substitution. That’s only possible with the core DML commands SELECT, INSERT, UPDATE, and DELETE. See: Error when setting n_distinct using a plpgsql variable You need dynamic SQL with EXECUTE: CREATE OR REPLACE FUNCTION loaddata(filepathname text) RETURNS void LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format (‘ COPY climatedata(climatestationid, date, …, tminsflag) … Read more
1. Implicit cursor It’s almost always better to use the implicit cursor of a FOR loop than to resort to a somewhat slower and unwieldy explicit cursor. I have written thousands of PL/pgSQL functions and only a hand full of times explicit cursors made any sense. CREATE OR REPLACE FUNCTION avoidable_states() RETURNS SETOF varchar LANGUAGE … Read more
Sanitize function What you currently have can be simplified / sanitized to: CREATE OR REPLACE FUNCTION func_a (username text=””, databaseobject text=””) RETURNS ???? LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY EXECUTE format (‘SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)’ , CASE WHEN username=”*” THEN ‘view1’ ELSE ‘view3’ END , databaseobject); END … Read more
Your function has a couple of smallint parameters. But in the call, you are using numeric literals that are presumed to be type integer. A string literal or string constant (‘123’) is not typed immediately. It remains type “unknown” until assigned or cast explicitly. However, a numeric literal or numeric constant is typed immediately. The … Read more
It is not supported to look beyond the current row in a CHECK constraint. http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says: A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns … Read more