Database Administrators Stack Exchange
Database Administrators Stack Exchange

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:

377 questions
74
votes
2 answers

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue to the column creation one that is solved with…
GuidoS
  • 897
  • 1
  • 6
  • 7
65
votes
4 answers

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I would like to do is move some of the application…
Derek Downey
  • 22,870
  • 11
  • 76
  • 103
49
votes
7 answers

What are the differences between "Stored Procedures" and "Stored Functions"?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't seem to apply (e.g. that they can be used in a…
DrColossos
  • 6,179
  • 2
  • 30
  • 30
49
votes
6 answers

How to insert (file) data into a PostgreSQL bytea column?

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This can, presumably, be done by one of the PL/…
SabreWolfy
  • 829
  • 1
  • 6
  • 16
41
votes
1 answer

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
MickeyfAgain_BeforeExitOfSO
  • 651
  • 1
  • 5
  • 11
41
votes
3 answers

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert at all to find out immediately, whether these…
shabunc
  • 707
  • 2
  • 6
  • 9
37
votes
1 answer

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line command? Like a,b := select col1,col2 from tbl where…
Xianlin
  • 537
  • 2
  • 6
  • 10
26
votes
1 answer

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN SELECT * FROM tablename WHERE condition LOOP …
skumar
  • 271
  • 1
  • 3
  • 7
24
votes
1 answer

Is there a good way to run a trigger for each record in a postgres table?

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed…
Joe
  • 4,909
  • 1
  • 26
  • 37
23
votes
2 answers

Postgres query plan of a function invocation written in plpgsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed inside a user defined function (UDF) using EXPLAIN. So how do I get hold of the query plan for a particular invocation of a UDF? I see the UDF…
Hassan Syed
  • 407
  • 3
  • 8
22
votes
2 answers

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > group_cur order by c.id desc limit 1; EXCEPTION …
icefex
  • 387
  • 1
  • 3
  • 9
21
votes
2 answers

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will…
Adam Matan
  • 10,129
  • 27
  • 75
  • 94
21
votes
2 answers

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: conceptional differences given a problem family, convenience…
Gismo Ranas
  • 325
  • 3
  • 9
18
votes
1 answer

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
FastTurtle
  • 283
  • 1
  • 2
  • 7
18
votes
3 answers

How can I return multiple rows of records in PL/pgSQL

I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD. that is, I want to append to rec so that rec becomes a set of rows when the loop is…
hky404
  • 293
  • 1
  • 2
  • 7
1
2 3
…
25 26