In cases like these, where the code is simple straightforward enough, sometimes it is useful to rely on one of these special plpgsql commands at the start of the function text: #variable_conflict error #variable_conflict use_variable #variable_conflict use_column In this case, it would be used as follows: CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date) RETURNS character varying … Read more
Dynamic SQL and RETURN type (I saved the best for last, keep reading!) You want to execute dynamic SQL. In principal, that’s simple in plpgsql with the help of EXECUTE. You don’t need a cursor. In fact, most of the time you are better off without explicit cursors. The problem you run into: you want … Read more
Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you’ll still get a wide dispersion of values which presumably is good enough for your problem. SQL functions derived from the other answers: For bigint: create … Read more
This has been asked many times. A possible solution can be found here: https://stackoverflow.com/a/6527838/552671 This solution requires both an UPDATE and INSERT. UPDATE table SET field=’C’, field2=’Z’ WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, ‘C’, ‘Z’ WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3); With Postgres 9.1 it is possible to … 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
DO $do$ BEGIN IF EXISTS (SELECT FROM orders) THEN DELETE FROM orders; ELSE INSERT INTO orders VALUES (1,2,3); END IF; END $do$ There are no procedural elements in standard SQL. The IF statement is part of the default procedural language PL/pgSQL. You need to create a function or execute an ad-hoc statement with the DO … Read more
Yes you can. There are a number of ways. 1) RETURN (SELECT …) CREATE OR REPLACE FUNCTION get_1(_param_id integer) RETURNS integer LANGUAGE plpgsql AS $func$ BEGIN RETURN _param_id; — Or: — RETURN (SELECT col1 FROM tbl WHERE id = _param_id); END $func$; 2) Use an OUT or INOUT parameter CREATE OR REPLACE FUNCTION get_2(_param_id integer, … Read more
All previously existing answers are outdated or were inefficient to begin with. Assuming you want to return three integer columns. PL/pgSQL function Here’s how you do it with modern PL/pgSQL (PostgreSQL 8.4 or later): CREATE OR REPLACE FUNCTION f_foo() — (open_id numeric) — parameter not used RETURNS TABLE (a int, b int, c int) AS … Read more
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
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