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
49
votes
3 answers

PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement

I've got this PL/pgSQL function which must return some users information. CREATE OR REPLACE FUNCTION my_function( user_id integer ) RETURNS TABLE( id integer, firstname character varying, lastname …
Getz
  • 3,983
  • 6
  • 35
  • 52
47
votes
8 answers

ERROR: unterminated quoted string at or near

While executing below shown trigger code using ANT I am getting the error org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code…
Anuj
  • 471
  • 1
  • 4
  • 3
46
votes
6 answers

Return setof record (virtual table) from function

I need a Postgres function to return a virtual table (like in Oracle) with custom content. The table would have 3 columns and an unknown number of rows. I just couldn't find the correct syntax on the internet. Imagine this: CREATE OR REPLACE…
David
  • 2,551
  • 3
  • 34
  • 62
46
votes
1 answer

Can I make a plpgsql function return an integer without using a variable?

Something like this: CREATE OR REPLACE FUNCTION get(param_id integer) RETURNS integer AS $BODY$ BEGIN SELECT col1 FROM TABLE WHERE id = param_id; END; $BODY$ LANGUAGE plpgsql; I would like to avoid a DECLARE just for this.
johnlemon
  • 20,761
  • 42
  • 119
  • 178
43
votes
3 answers

PL/pgSQL perform vs execute

What are the difference between perform and execute on PL/pgSQL? From the manual: Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful…
Guy s
  • 1,586
  • 3
  • 20
  • 27
42
votes
2 answers

It could refer to either a PL/pgSQL variable or a table column

I have a function in pgsql CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date) RETURNS character varying AS $$ BEGIN RETURN( SELECT date_in_bs FROM core.date_conversion WHERE date_in_ad = $1 ); END $$ …
mban94
  • 683
  • 1
  • 10
  • 18
42
votes
4 answers

Refactor a PL/pgSQL function to return the output of various SELECT queries

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and return the result - just like the query itself…
waldyr.ar
  • 14,424
  • 6
  • 33
  • 64
41
votes
2 answers

PostgreSQL cannot begin/end transactions in PL/pgSQL

I am seeking clarification of how to ensure an atomic transaction in a plpgsql function, and where the isolation level is set for this particular change to the database. In the plpgsql function shown below, I want to make sure that BOTH the deletion…
Tim
  • 8,669
  • 31
  • 105
  • 183
41
votes
6 answers

How to create sequence if not exists

I tried to use code from Check if sequence exists in Postgres (plpgsql). To create sequence if it does not exists. Running this code two times causes an exception: sequence ... already exists. How to create sequence only if it does not exist? If…
Andrus
  • 26,339
  • 60
  • 204
  • 378
39
votes
3 answers

Function with SQL query has no destination for result data

I am trying to create a function that returns a SELECTed resultset. When I call my postgres function like this select * from tst_dates_func() I get an error as shown below: ERROR: query has no destination for result data HINT: If you want to…
Horse Voice
  • 8,138
  • 15
  • 69
  • 120
39
votes
5 answers

How to perform a select query in a DO block?

I want to port the below SQL code from MS SQL-Server to PostgreSQL. DECLARE @iStartYear integer DECLARE @iStartMonth integer DECLARE @iEndYear integer DECLARE @iEndMonth integer SET @iStartYear = 2012 SET @iStartMonth = 4 SET @iEndYear = 2016 SET…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
38
votes
5 answers

No function matches the given name and argument types

My function is: CREATE OR REPLACE FUNCTION FnUpdateSalegtab09 ( iacyrid Integer,iRepId Integer,iDrId Integer,ivrid Integer,imode smallint,itrno varchar,itrdate timestamp,iacid Integer,ivrno varchar,iSuppId Integer,icustname varchar,inetamt…
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
38
votes
2 answers

FOR EACH STATEMENT trigger example

I've been looking at the documentation of postgresql triggers, but it seems to only show examples for row-level triggers, but I can't find an example for a statement-level trigger. In particular, it is not quite clear how to iterate in the…
diffeomorphism
  • 991
  • 2
  • 10
  • 27
38
votes
3 answers

SELECT INTO with more than one attribution

This instruction works: SELECT INTO unsolvedNodes array_agg(DISTINCT idDestination) FROM road WHERE idOrigin = ANY(solvedNodes) AND NOT (idDestination = ANY(solvedNodes)); But I would like to use something this way: SELECT INTO unsolvedNodes…
felipe.zkn
  • 2,012
  • 7
  • 31
  • 63
37
votes
2 answers

Loop on tables with PL/pgSQL in Postgres 9.0+

I want to loop through all my tables to count rows in each of them. The following query gets me an error: DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY…
Totor
  • 483
  • 1
  • 4
  • 5
1 2
3
…
99 100