New answers tagged

1

I worked out how to do it using unnest(...) with ordinality and returning an array as part of the results. Here is the function definition: create function summarize_tables(categories text[], variadic tbls name[]) returns table (tbl name, overall text, by_category text[]) language plpgsql as $funcdef$ begin foreach tbl in array tbls loop ...


0

Great post, I have had to deal with the problem with a table that can grow and shrink the number of columns. I did the same thing you did and that is dynamically construct the SQL to create the new table. the problem becomes how to return the table in a simple query from a function. Flat out one can not do that. so the Solution is very simple pass ...


0

You described the problem well. I cannot give a complete solution, but I'll give you a few ideas. As you have found out, a function where the number of result columns is variable cannot be done the way you want; if you define it as RETURNS SETOF record, you'll have to specify the actual result columns in every query to the function. This is because the ...


1

You can return a row type by using an expression with that type. here it's effectively select my_table from my_table create function my_func4(f1_to_look_up int) returns my_table language plpgsql as $$ begin return my_table from my_table where f1 = f1_to_look_up; end; $$; or via a variable create or replace function my_func4(f1_to_look_up ...


0

I figured it out while writing the question; so far as I can tell this is the only way to do it: create function my_func3(f1_to_look_up int, out my_result my_table) language plpgsql as $$ begin select * into strict my_result from my_table where f1 = f1_to_look_up; return; end $$;


0

TG_TABLE_SCHEMA is not set in event triggers. You'll have to call pg_event_trigger_dropped_objects() to get information about the dropped objects. Among the result columns is schema_name, which contains the schema name of the affected object.


0

Here is how to do it without superuser privileges (such as on Heroku). \lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf' update contracts set contract = lo_get(:LASTOID) where id = 77; You can use \lo_list to see the large objects, and \lo_unlink to delete them. The contract field in my example is bytea. Column | Type ...


Top 50 recent answers are included