#plpgsql

http://stackoverflow.com/a/16688055/1964078

but you can’t also RETURN QUERY on an anonymous function. so answer is to create a temp table:

i’m able to successfully generate a set of records in pl, but now trying to iterate over each row and create new rows using a loop

some interesting clues here:

CREATE OR REPLACE FUNCTION insert_trg_func() RETURNS TRIGGER AS $$ DECLARE name text; BEGIN  FOR i IN 0..5 LOOP    IF pst.record_get_field(new, 'a'||i) IS NULL THEN      new := pst.record_set_fields(new, 'a'||i, -1000);    END IF;  END LOOP; r  RETURN new; END; $$ LANGUAGE plpgsql;

source

got the loop working, now need to cast result to a specific type, some clues here:

CREATE TYPE my_type (f1 varchar(10), f2 varchar(10), ... );CREATE OR REPLACE FUNCTION get_object_fields(name text) RETURNS my_type AS $$ DECLARE  result_record my_type; BEGIN  SELECT f1, f2, f3  INTO result_record.f1, result_record.f2, result_record.f3  FROM table1  WHERE pk_col = 42;  SELECT f3  INTO result_record.f3  FROM table2  WHERE pk_col = 24;  RETURN result_record; END $$ LANGUAGE plpgsql;

found the solution. the goal is the following:

turn this query result:

1,10 3,20

into this query result:

1,10 2,10 3,10

at first thought i should create a function whose argument is record[1,10] and is called within the query and loops through the records and turns them into two separate records dynamically.

but that was overthinking it; instead, it's easier to create a method that accepts query and loops through all the records and creates a whole NEW query instead.

that whole new query can be inserted into another materialized view, which is then eventually joined with the original table.

here are some samples that do that:

http://stackoverflow.com/questions/22283282/pl-pgsql-functions-loop-through-specific-column-and-execute-second-query-in-lo

http://stackoverflow.com/questions/16676573/returning-results-from-a-function-in-select-statement-format/16677220#16677220

http://stackoverflow.com/questions/22817904/syntax-error-in-pl-pgsql-for-loop?rq=1

starting effort, this only returns 1 column:

CREATE OR REPLACE FUNCTION estimate_rows() RETURNS SETOF text LANGUAGE plpgsql AS $func$ DECLARE rec record; BEGIN FOR rec IN select acs_id , date_part('days', f.period_end - reldate) + 1 days , f.value_subunit from flows f inner join ( select acs.id acs_id , min(f.period_start) reldate from flows f inner join actual_sheets acs on f.actual_sheet_id = acs.id group by acs.id ) reldates on f.actual_sheet_id = reldates.acs_id LOOP RETURN NEXT rec.days; END LOOP; END $func$ ;

using a newly created type, i can now output multiple columns:

I’m just starting to learn PLPGSQL, (procedural language postgresql) based on pl/sql, a procedural language developed by Oracle to extend the functionality of their dbms.

from strictly my experience, there are two new developments in postgres that allow me to push more of my business logic to my database:

materialized views now allows me to use my database to cache data, rather than using some other 3rd party technology in the app layer

advanced json capabilities in postgres now make it easier to construct complex json representations in the db before pushing it to the app