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
2
votes
2answers
988 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
12k 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
4k views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
2
votes
1answer
633 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; ...
2
votes
1answer
2k 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 ...
1
vote
2answers
15k 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 ...
1
vote
2answers
840 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
3answers
9k 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
2k 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: (...
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
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
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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
493 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
1answer
806 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
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
699 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(...
1
vote
1answer
58 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 ...
0
votes
1answer
59 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 ...