Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for the PostgreSQL database system. Questions about PL/pgSQL should probably be tagged "PostgreSQL" as well.

Filter by
Sorted by
Tagged with
3
votes
2answers
3k views

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not ...
3
votes
1answer
514 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
2
votes
2answers
1k views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
2
votes
1answer
2k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
2
votes
2answers
22k views

RETURN value directly from INSERT with RETURNING clause

I have a function that ends with: INSERT INTO configuration_dates ( cols... ) VALUES ( values... ) RETURNING id INTO ret_id; RETURN ret_id; And I would like to remove the ...
2
votes
1answer
2k views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
2
votes
1answer
440 views

Dealing with out-of-range values on a PARTITION BY RANGE

I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE. There are 5 partitions at the moment, containing a span of a month each, starting ...
2
votes
1answer
4k views

ERROR: “sql ” is not a known variable

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running. I'm invoking the cobertura.sql file using ...
2
votes
1answer
14k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
1answer
3k views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
2
votes
1answer
736 views

trigger can't access column loop

SSCCE: The following script: $ cat test.sql CREATE TABLE public.foo ( loop INTEGER ); CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$ SELECT TRUE; $$ LANGUAGE SQL; ...
1
vote
2answers
5k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
1
vote
1answer
133 views

Surprising results from assigning multiple variables in FOR loop

CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ DECLARE _key text; _value text; BEGIN FOR _key, _value IN ...
1
vote
2answers
70 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
1
vote
1answer
1k views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
1
vote
1answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
1
vote
3answers
17k views

Looping with array of values in PL/pgSQL

Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE? FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', ...
1
vote
2answers
1k views

Insert values from a record variable into a subclass table

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
1
vote
1answer
656 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
1
vote
1answer
848 views

Make query depend on input in PostgreSQL function

I have a simple query: CREATE OR REPLACE FUNCTION companies_all_teams( IN _company_name text = NULL, IN _company_id int = NULL, OUT _result text ) AS $$ BEGIN _result := (SELECT json_agg(...
1
vote
2answers
3k views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (...
0
votes
1answer
62 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
0answers
29 views

How to get TABLE like dynamic structure output/returned from PostgreSQL function?

I am not very experienced with PLPGSSQL hence seeking some guidance to achieve following... I have an Oracle Procedure which i have to convert to PostgreSQL. This oracle function has declared a TABLE ...

1
2