Linked Questions

1
vote
2answers
4k views

Postgres declare table as a parameter for a function?

I would like to create a function that operates on a table, for example create or replace function test(t table) returns void language plpgsql as $func$ begin select * from t limit 10; ...
0
votes
2answers
4k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
6
votes
1answer
20k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
6
votes
1answer
11k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
5
votes
1answer
2k views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
5
votes
1answer
3k views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
4
votes
1answer
188 views

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

Is there a difference between a plain SQL function doing a SQL query: create function get_sports() returns setof sport as $body$ select * from sport; $body$ language sql stable; and PLPGSQL ...
2
votes
1answer
5k views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
2
votes
1answer
6k views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable +----+----------------...
2
votes
1answer
2k views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
1
vote
1answer
52 views

Update dynamic column names using keys from jsonb argument

I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
1
vote
1answer
40 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
1
vote
1answer
938 views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $...
0
votes
1answer
1k views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to z)....
-3
votes
1answer
5k views

How to use variable value inside copy function [closed]

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable. COPY (SELECT cname.portal from user) To '/tmp/out.csv' ...

15 30 50 per page