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
37
votes
2 answers

BREAK statement in PL/pgSQL

How to have the break statement in PostgreSQL? I have the structure like this: for() { for() { if(somecondition) break; } } As per my understanding it should only break the inner for loop?
user1844840
  • 1,937
  • 3
  • 16
  • 13
37
votes
2 answers

PostgreSQL table variable

Is there anything like table variables in T-SQL? In Sql Server it looks like this: DECLARE @ProductTotals TABLE ( ProductID int, Revenue money ) Then in procedure I can: INSERT INTO @ProductTotals (ProductID, Revenue) SELECT ProductID,…
Yavanosta
  • 1,480
  • 3
  • 18
  • 27
36
votes
2 answers

INSERT with dynamic table name in trigger function

I'm not sure how to achieve something like the following: CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$ DECLARE shadowname varchar := TG_TABLE_NAME || 'shadow'; BEGIN INSERT INTO shadowname…
sschober
  • 2,003
  • 3
  • 24
  • 38
36
votes
1 answer

SELECT .. INTO to create a table in PL/pgSQL

I want to use SELECT INTO to make a temporary table in one of my functions. SELECT INTO works in SQL but not PL/pgSQL. This statement creates a table called mytable (If orig_table exists as a relation): SELECT * INTO TEMP TABLE mytable FROM…
nnyby
  • 4,748
  • 10
  • 49
  • 105
35
votes
1 answer

PL/pgSQL function - Catch errors

I am writing an function with exception catching and ignoring. I want to catch all the exceptions and just ignore it. Is there anyway to catch all the exceptions and not individually? CREATE OR REPLACE FUNCTION ADD_TABLE_TO_ARCHIVE (a TEXT, b…
Pavan Ebbadi
  • 852
  • 1
  • 13
  • 26
35
votes
8 answers

Liquibase error [Postgresql]: unterminated dollar-quoted string at or near "$BODY$

Liquibase error: unterminated dollar-quoted string at or near "$BODY$` Chaneg log xml has one entry: see below include file="/home/dev/....../admin_script.sql" content of the file: ............... CREATE OR REPLACE FUNCTION…
Vibin
  • 489
  • 1
  • 5
  • 10
35
votes
2 answers

Return value from anonymous function postgresql

How to? For easy example. I have a simple function: DO LANGUAGE plpgsql $$ DECLARE BEGIN EXECUTE 'SELECT NOW()'; END $$; How I can return value of "NOW()" or other values from also anonymous function? The function is given as an example I have a…
arturgspb
  • 1,004
  • 1
  • 12
  • 19
34
votes
1 answer

Postgres trigger after insert accessing NEW

I have a pretty simple trigger: CREATE OR REPLACE FUNCTION f_log_datei() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id); END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER log_datei…
soupdiver
  • 3,504
  • 9
  • 40
  • 68
33
votes
3 answers

How to prevent 'invalid input syntax for type json' in Postgres, when records contain a mix of json or strings

I have a text column that contains JSON and also plan text. I want to convert it to JSON, and then select a particular property. For example: user_data _________ {"user": {"name": "jim"}} {"user": {"name": "sally"}} some random data string I've…
Allyl Isocyanate
  • 13,306
  • 17
  • 79
  • 130
33
votes
5 answers

Return zero if no record is found

I have a query inside a stored procedure that sums some values inside a table: SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res; After this select I subtract res value with an integer retrieved by another query and return the result. If…
giozh
  • 9,868
  • 30
  • 102
  • 183
33
votes
3 answers

Is SELECT or INSERT in a function prone to race conditions?

I wrote a function to create posts for a simple blogging engine: CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[]) RETURNS INTEGER AS $$ DECLARE InsertedPostId INTEGER; TagName VARCHAR; BEGIN INSERT INTO Posts…
user142019
32
votes
6 answers

PostgreSQL PL/pgSQL random value from array of values

How can I declare an array like variable with two or three values and get them randomly during execution? a := [1, 2, 5] -- sample sake select random(a) -- returns random value Any suggestion where to start?
Mo J. Mughrabi
  • 6,747
  • 16
  • 85
  • 143
32
votes
3 answers

Optional argument in PL/pgSQL function

I am trying to write a PL/pgSQL function with optional arguments. It performs a query based on a filtered set of records (if specified), otherwise performs a query on the entire data set in a table. For example (PSEUDO CODE): CREATE OR REPLACE…
Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341
31
votes
1 answer

How to create guid in PostgreSQL

How to create GUID in Windows format in Postgres 9.0+? I tried function CREATE or REPLACE FUNCTION public.getguid() RETURNS varchar AS $BODY$ DECLARE v_seed_value varchar(32); BEGIN select md5( inet_client_addr()::varchar || …
Andrus
  • 26,339
  • 60
  • 204
  • 378
31
votes
4 answers

Pass a SELECT result as an argument to postgreSQL function

I have a table "UserState" with following fields: id, userid, ctime, state, endtime. I have a simple query: SELECT userid FROM "UserState" WHERE ctime>'2014-07-14'::timestamp AND I have a plpgsql function, which must take the result of this query…
user3824666
  • 333
  • 1
  • 5
  • 11
1 2 3
…
99 100