#plpgsql

[info-comp]

Как реализовать журнал DDL операций в PostgreSQL? Пример создания DDL Log | Info-Comp.ru - IT-блог для начинающих

Из данного материала Вы узнаете, как создать DDL лог в PostgreSQL – это журнал DDL операций, который мы будем реализовывать с помощью тригге

В данном материале рассмотрен пример реализации DDL лога в PostgreSQL с использованием триггеров событий.

[info-comp]

В данном материале рассмотрено несколько способов создания табличных функций в PostgreSQL, т.е. функций, которые возвращают табличные данные. Приведены примеры создания функций как на языке SQL, так и на языке PL/pgSQL

Создание табличных функций в PostgreSQL на SQL и PL/pgSQL | Info-Comp.ru - IT-блог для начинающих

Из данного материала Вы узнаете, как в PostgreSQL создать табличную функцию, т.е. функцию, возвращающую табличные данные. Рассмотрены пример

[banker-hacker]

#plpgSQL function can't just run a query; you have to put the results somewhere.

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:

http://www.ienablemuch.com/2010/12/return-results-from-anonymous-code.html

[banker-hacker]

#plpgsql in @rails

https://www.amberbit.com/blog/2014/2/4/postgresql-awesomeness-for-rails-developers/

[banker-hacker]

#plpgsql how to join a function with a table

http://grokbase.com/t/postgresql/pgsql-general/118gdpwwp9/join-between-a-table-and-function

[banker-hacker]

functions in #plpgsql cont’d

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;

source

[banker-hacker]

how to loop through #sql query using #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:

CREATE TYPE filler_rows AS (acs_id int, days int, value_subunit bigint); CREATE OR REPLACE FUNCTION estimate_rows() RETURNS SETOF filler_rows LANGUAGE plpgsql AS $func$ DECLARE rec filler_rows; 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; END LOOP; END $func$ ;

[banker-hacker]

if my experience is indicative, #plpgsql will become more popular.

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

i for one am experimenting with pushing more of the logic to my db, because it just seems faster.