You can use the alternative syntax with a array literal instead of the array constructor, which is a Postgres function-like construct and may cause trouble when you need to pass values – like in a prepared statement: SELECT myschema.myfunc(‘0d6311cc-0d74-4a32-8cf9-87835651e1ee’ , ‘{“(0d6311cc-0d74-4a32-8cf9-87835651e1ee, 25)” , “(6449fb3b-844e-440e-8973-31eb6bbefc81, 10)”}’::mytype[]); I added a line break between the two row types … Read more
I don’t think you can do this cheaply with a plain query, CTEs and window functions – their frame definition is static, but you need a dynamic frame (depending on column values). Generally, you’ll have to define lower and upper bound of your window carefully: The following queries exclude the current row and include the … 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
This construct is not possible: IF EXECUTE ‘EXISTS (SELECT 1 FROM mytable)’ THEN … You can simplify to: IF EXISTS (SELECT 1 FROM mytable) THEN … But your example is probably simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check the special variable FOUND immediately after executing any DML command … Read more
You cannot use a variable in place of an identifier like that. You need to do it with dynamic queries. It will look something like this: EXECUTE ‘SELECT * FROM ‘ || quote_ident(tname) || ‘ WHERE ‘ || quote_ident(cname) || ‘ NOT IN (”AK”,”CK”);’ INTO result_var; If you are using PostgreSQL 9.1 or above, you … Read more
A simpler way has been around since PostgreSQL 8.3: CREATE FUNCTION test() RETURNS SETOF first_table AS $func$ BEGIN RETURN QUERY SELECT * FROM first_table; RETURN QUERY SELECT * FROM second_table; — has to return same rowtype as first_table! END $func$ LANGUAGE plpgsql; Call: SELECT * FROM test(); Both result sets are appended to a single … 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
Generally, to decompose rows returned from a function and get individual columns: SELECT * FROM account_servicetier_for_day(20424, ‘2014-08-12’); As for the query: Postgres 9.3 or newer Cleaner with JOIN LATERAL: SELECT ‘2014-08-12’ AS day, 0 AS inbytes, 0 AS outbytes , a.username, a.accountid, a.userid , f.* — but avoid duplicate column names! FROM account_tab a , … Read more
Erwin sent a correct reply. My next reply is related to possibility to disable overloading. It is not possible to disable overloading – this is a base feature of PostgreSQL function API system – and cannot be disabled. We know so there are some side effects like strong function signature rigidity – but it is … Read more
I think you’re looking for SELECT select_expressions INTO: select test_table.name into name from test_table where id = x; That will pull the name from test_table where id is your function’s argument and leave it in the name variable. Don’t leave out the table name prefix on test_table.name or you’ll get complaints about an ambiguous reference.