How can I send some http request from postgresql function or trigger
You could try writing the trigger in PL/Python and use urllib2 to POST.
plpgsql
ERROR: unterminated quoted string at or near
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
Iterating over integer[] in PL/pgSQL
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
Cursor based records in PostgreSQL
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
No function matches the given name and argument types
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
SQL Sub queries in check constraint
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
PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
How are you executing that function? It works as a select statement. Create a table: public.users create table public.users (id int, firstname varchar, lastname varchar); Insert some records: insert into public.users values (1, ‘aaa’,’bbb’),(2,’ccc’,’ddd’); function: my_function CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$ DECLARE ids … Read more
COPY with dynamic file name
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
Function to loop through and select data from multiple tables
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
Return multiple columns and rows from a function PostgreSQL instead of record
Functions returning a table (or setof) should be used in the FROM clause: select * from brand_hierarchy(1234)