Stack Overflow
Stack Overflow

Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.

PL/pgSQL is the default procedural language for PostgreSQL. It can be used to write user defined functions and trigger functions. Since PostgreSQL 9.0, PL/pgSQL is installed by default. It can be used in anonymous code blocks using the DO statement, in functions and in procedures (since Postgres 11).

Reference:

4245 questions
31
votes
6 answers

Check if sequence exists in Postgres (plpgsql)

I'm trying to test, within a stored procedure, whether a sequence already exists. IF EXISTS SEQUENCE seq_name RAISE EXCEPTION 'sequence % already exists!', seq_name END IF; I have tried several variations of the snippet above without luck. I…
Ilia Choly
  • 18,070
  • 14
  • 92
  • 160
30
votes
2 answers

How to use a record type variable in plpgsql?

How can I use query result stored into a record type variable for another query within the same stored function? I use Postgres 9.4.4. With a table like this: create table test (id int, tags text[]); insert into test values (1,'{a,b,c}'), …
SG. Nihonbashi
  • 471
  • 1
  • 4
  • 7
30
votes
4 answers

Good resources for learning PL/pgSQL?

I've been looking around the net trying to find good resources for learning PostgreSQL's procedural programming language, PL/pgSQL. So far the only thing I've managed to dig up is the tutorial in the PostgreSQL documentation. While that is good,…
mikl
  • 23,749
  • 20
  • 68
  • 89
29
votes
4 answers

Two closest points on boundary of Postgis geometry

I have a table geofences which stores geometry of polygon. I also have a point A which is inside the geometry. What I have to do is find the two closest points from point A that lie on the surface of the polygon geometry. Function in…
Satish Sharma
  • 3,284
  • 9
  • 38
  • 51
28
votes
1 answer

PostgreSQL CASE usage in functions

Can't we use CASE condition outside SQL SELECT statements? E.g.: CASE WHEN old.applies_to = 'admin' THEN _applies_to = 'My Self' ELSE _applies_to = initcap(old.applies_to) END _summary = _summary || '
  • Apply To: ' || _applies_to ||…
    • Yohan Hirimuthugoda
    • 1,053
    • 3
    • 11
    • 20
    28
    votes
    2 answers

    Unnest multiple arrays in parallel

    My last question Passing an array to stored to postgres was a bit unclear. Now, to clarify my objective: I want to create an Postgres stored procedure which will accept two input parameters. One will be a list of some amounts like for instance (100,…
    Maki
    • 471
    • 2
    • 8
    • 17
    28
    votes
    1 answer

    Disable DELETE on table in PostgreSQL?

    For a security sensitive design, I'd like to disable DELETEs on certain tables. The DELETE should merely set a deleted flag on a row (which would be then visible on a view, which would be used by the application layer). As I understand a rule would…
    miku
    • 181,842
    • 47
    • 306
    • 310
    26
    votes
    2 answers

    array_length() of an empty array returning NULL

    I'm developing some stored proceduces in PL/pgSQL and some of them are giving me some problems. The sprocs I'm developing receive by parameter an array which I use in a FOR LOOP to get all its elements. To define the upper bound of the FOR LOOP I…
    JMagalhaes
    • 295
    • 2
    • 4
    • 7
    25
    votes
    1 answer

    What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

    In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which? In this very simple example, do they work the same, correct?…
    user3284469
    25
    votes
    1 answer

    Declare row type variable in PL/pgSQL

    As I found SELECT * FROM t INTO my_data; works only if: DO $$ DECLARE my_data t%ROWTYPE; BEGIN SELECT * FROM t INTO my_data WHERE id = ?; END $$; Am I right? If I want to get only 2-3 columns instead of all columns. How can I define my_data? That…
    Vyacheslav
    • 26,359
    • 19
    • 112
    • 194
    25
    votes
    3 answers

    drop all tables sharing the same prefix in postgres

    I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.
    Roy
    • 723
    • 2
    • 8
    • 21
    25
    votes
    2 answers

    Declare variable set = select

    How do I declare a variable for used in a PostgreSQL 9.3 query? CREATE or replace FUNCTION public.test() returns int4 AS $BODY$ DECLARE cod_process bigint :=30001; cod_instance bigint ; utc_log timestamp without time zone :=…
    ASA
    • 291
    • 1
    • 5
    • 11
    25
    votes
    4 answers

    Dynamically generate columns for crosstab in PostgreSQL

    I am trying to create crosstab queries in PostgreSQL such that it automatically generates the crosstab columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my crosstab query. The idea…
    invinc4u
    • 1,125
    • 3
    • 15
    • 26
    25
    votes
    2 answers

    plpgsql: calling a function with 2 OUT parameters

    I'm trying to fetch to values from a plpgsql function with 2 OUT paramenters but I have some problem. These are the functions: CREATE OR REPLACE FUNCTION get_test(OUT x text, OUT y text) AS $$ BEGIN x := 1; y := 2; END; $$ LANGUAGE…
    marco
    • 1,686
    • 1
    • 25
    • 33
    24
    votes
    5 answers

    ERROR: must be owner of language plpgsql

    I'm using PostgreSQL v9.0.1 with Rails (and it's deps) @ v2.3.8, owing to the use of the fulltext capability of postgres, I have a table which is defined as: CREATE TABLE affiliate_products ( id integer NOT NULL, name character…
    Lee Hambley
    • 6,270
    • 5
    • 49
    • 81
    1 2 3
    …
    99 100