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)
foreach tbl in array tbls
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 ...
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 ...
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
return my_table from my_table where f1 = f1_to_look_up;
or via a variable
create or replace function my_func4(f1_to_look_up ...
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)
select * into strict my_result from my_table where f1 = f1_to_look_up;
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.
Here is how to do it without superuser privileges (such as on Heroku).
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 ...