You can use the provided C function crosstab_hash for this. The manual is not very clear in this respect. It’s mentioned at the end of the chapter on crosstab() with two parameters: You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples … Read more
Procedural elements like loops are not part of the SQL language and can only be used inside the body of a procedural language function, procedure (Postgres 11 or later) or a DO statement, where such additional elements are defined by the respective procedural language. The default is PL/pgSQL, but there are others. Example with plpgsql: … 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
To get individual columns instead of the row type, call the function with: SELECT * FROM testfunction(); Just like you would select all columns from a table. Also consider this reviewed form of your test function: CREATE OR REPLACE FUNCTION testfunction() RETURNS TABLE(a int, b int) LANGUAGE plpgsql AS $func$ DECLARE _a int := 0; … 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
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
Basic query This query creates all necessary DDL statements: SELECT ‘DROP FUNCTION ‘ || oid::regprocedure FROM pg_proc WHERE proname=”my_function_name” — name without schema-qualification AND pg_function_is_visible(oid); — restrict to current search_path Output: DROP FUNCTION my_function_name(string text, form text, maxlen integer); DROP FUNCTION my_function_name(string text, form text); DROP FUNCTION my_function_name(string text); Execute the commands after checking plausibility. … Read more
The manual: Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement: PERFORM query; Unless you assign the result, replace SELECT 1; with PERFORM 1;
It would work like this: CREATE OR REPLACE FUNCTION name_function(param_1 varchar , OUT param_2 bigint) LANGUAGE plpgsql AS $func$ BEGIN INSERT INTO table (collumn_seq, param_1) — “param_1” also the column name? VALUES (DEFAULT, param_1) RETURNING collumn_seq INTO param2; END $func$; Normally, you would add a RETURN statement, but with OUT parameters, this is optional. Refer … Read more