Tag Info

New answers tagged

2

This can be considerably simpler and faster: DO $do$ BEGIN EXECUTE ( SELECT E'CREATE TABLE t_new AS\n' || string_agg(format('SELECT tableoid::regclass AS source_table, * FROM %s' , c.oid::regclass::text) ,E'\nUNION ALL\n' ORDER BY c.oid::regclass::text) FROM pg_namespace n JOIN ...


2

If you want to have some magic which does this automatically, you can use a function that dynamically creates a select statement and returns a union of all tables: create or replace function get_all() returns table (data_source text, id integer) as $body$ declare full_query text := ''; table_rec record; row_nr integer := 1; begin for ...


2

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


0

Great dezso, it works! Here is the final version of my function: CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$ DECLARE dataset_1_row RECORD; --Record variable to go through each row of the view below update_query TEXT; --The dynamic UPDATE query to be executed BEGIN FOR dataset_1_row IN --Cycle through rows of query ...


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


2

Here's an article I wrote for how to get one installed on Ubuntu. Installation instructions for the pldebugger for PostgreSQL pg_config --version "9.3beta2" Look here for source code matching your version of PostgreSQL: ftp://ftp.postgresql.org/pub/source/ For me, it was: wget ftp://ftp.postgresql.org/pub/source/v9.3beta2/postgresql-9.3beta2.tar.bz2 ...



Top 50 recent answers are included