Tag Info

New answers tagged

0

A recursive CTE seems the way to go. Assuming your path has no cycles. Else it needs more work to detect cycles. The array solution below can readily be adapted. Test setup Building on this simplified layout: CREATE TABLE t1 (t1_id int, objid text); INSERT INTO t1 VALUES (1,'aaa') ,(2,'bbb') ,(3,'ccc') ,(4,'ddd') ,(5,'eee') ,(6,'fff') ,(7,'ggg') ...


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 ...



Top 50 recent answers are included