Yes, that is a feature of Postgres and parallel unnesting is guaranteed to be in sync (as long as all arrays have the same number of elements). Postgres 9.4 adds a clean solution for parallel unnest: Unnest multiple arrays in parallel The order of resulting rows is not guaranteed, though. Actually, with a statement as … 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
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
Table functions (functions defined as returns table or returns setof) need to be used in the from clause like a table. So you need to use: select * from get_avg_prices(…); Only scalar functions (functions which return only a single value, e.g. a number) should be put into the select list.
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
First of all, you cannot access JSON array values like that. For a given json value: [{“event_slug”:”test_1″,”start_time”:”2014-10-08″,”end_time”:”2014-10-12″}, {“event_slug”:”test_2″,”start_time”:”2013-06-24″,”end_time”:”2013-07-02″}, {“event_slug”:”test_3″,”start_time”:”2014-03-26″,”end_time”:”2014-03-30″}] A valid test against the first array element would be: WHERE e->0->>’event_slug’ = ‘test_1′ But you probably don’t want to limit your search to the first element of the array. With the jsonb data type in … Read more
Clean setup: CREATE TABLE tbl ( given_date date , set_name varchar ); Use a singular term as column name for a single value. The data type is obviously date and not a timestamp. To transform your text parameters into a useful table: SELECT unnest(string_to_array(‘2001-01-01to2001-01-05,2001-01-10to2001-01-15’, ‘,’)) AS date_range , unnest(string_to_array(‘s1,s2’, ‘,’)) AS set_name; “Parallel unnest” is … Read more
In Postgres 9.3 or later, it’s typically best to use LEFT JOIN LATERAL … ON true: SELECT sub.dataid, f.* FROM ( SELECT dataid, array_agg(data) AS arr FROM dataset WHERE dataid = something GROUP BY 1 ) sub LEFT JOIN LATERAL foo(sub.arr) f ON true; If the function foo() can return no rows, that’s the safe … Read more
In Postgres 9.3+ use a LATERAL join. Minimal form: SELECT token, flag FROM tbl, unnest(string_to_array(subject, ‘ ‘)) token WHERE flag = 2; The comma in the FROM list is (almost) equivalent to CROSS JOIN, LATERAL is automatically assumed for set-returning functions (SRF) in the FROM list. Why “almost”? See: “invalid reference to FROM-clause entry for … Read more
Return selected columns CREATE OR REPLACE FUNCTION get_user_by_username(_username text , _online bool DEFAULT false) RETURNS TABLE ( user_id int , user_name varchar , last_activity timestamptz ) LANGUAGE plpgsql AS $func$ BEGIN IF _online THEN RETURN QUERY UPDATE users u SET last_activity = current_timestamp — ts with time zone WHERE u.user_name = _username RETURNING u.user_id , … Read more