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

Why is PostgreSQL array access so much faster in C than in PL/pgSQL?

I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following: CREATE TABLE foo (stuff INT[]); INSERT INTO foo VALUES ({ 1, 2, 3 }); INSERT INTO foo…
Matt Solnit
  • 32,152
  • 8
  • 53
  • 57
23
votes
5 answers

PostgreSQL function returning multiple result sets

Is it possible to return multiple result sets from a Postgres function, like in MSSQL: CREATE PROCEDURE test AS SELECT * FROM first_table SELECT * FROM second_table
Markus
  • 3,547
  • 10
  • 39
  • 55
23
votes
6 answers

Using pg_notify in PostgreSQL trigger function

I am attempting to issue a notification from a PostgreSQL trigger function. I can successfully use the NOTIFY command, but I am not having any luck with pg_notify. Even though I receive a notification when I invoke the pg_notify function from the…
Tom
  • 335
  • 1
  • 2
  • 7
23
votes
3 answers

How can I send some http request from postgresql function or trigger

I need to send data via http protocol (GET or POST request) from the function or trigger. Is it possible?
im4LF
  • 275
  • 1
  • 4
  • 10
23
votes
3 answers

Variable containing the number of rows affected by previous DELETE? (in a function)

I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the concept. DECLARE re1…
Bruno Bronosky
  • 66,273
  • 12
  • 162
  • 149
23
votes
4 answers

How to find if a function exists in PostgreSQL?

Unlike tables or sequences, user-defined functions cannot be found through pg_class. There are questions on how find a list of all functions to delete or grant them, but how to find an individual function (with known name and argument types) is not…
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
23
votes
1 answer

Define table and column names as arguments in a plpgsql function?

It must be simple, but I'm making my first steps into Postgres functions and I can't find anything that works... I'd like to create a function that will modify a table and / or column and I can't find the right way of specifying my tables and…
fgcarto
  • 335
  • 1
  • 2
  • 8
22
votes
5 answers

PostgreSQL parameterized Order By / Limit in table function

I have a sql function that does a simple sql select statement: CREATE OR REPLACE FUNCTION getStuff(param character varying) RETURNS SETOF stuff AS $BODY$ select * from stuff where col = $1 $BODY$ LANGUAGE sql; For now I am invoking…
JoshuaBoshi
  • 1,266
  • 1
  • 14
  • 24
22
votes
3 answers

Unnest array by one level

I want to take an array of n dimensions and return set containing rows of arrays of n-1 dimensions. For example, take the array ARRAY[[1,2,3], [4,5,6], [7,8,9]] and return a set {1,2,3}, {4,5,6}, {7,8,9}. Using unnest returns the set…
Matt
  • 4,515
  • 5
  • 22
  • 29
22
votes
3 answers

Raising error in postgreSQL

CREATE OR REPLACE FUNCTION msgfailerror() RETURNS trigger AS ' BEGIN IF NEW.noces< new.first_column THEN RAISE EXCEPTION 'cannot have a negative salary'; END IF; return new; END' LANGUAGE plpgsql Trigger create trigger…
user1686308
  • 305
  • 2
  • 3
  • 5
21
votes
3 answers

Escape function for regular expression or LIKE patterns

To forgo reading the entire problem, my basic question is: Is there a function in PostgreSQL to escape regular expression characters in a string? I've probed the documentation but was unable to find such a function. Here is the full problem: In a…
Benny
  • 1,508
  • 3
  • 18
  • 34
21
votes
1 answer

plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

I have a plpgsql function in PostgreSQL 9.2 which returns a table. The function runs several SELECTs that return the same columns as the function and then either returns those results or raises an exception, depending on some checks. The only way I…
EM0
  • 5,369
  • 7
  • 51
  • 85
21
votes
2 answers

Getting Affected Rows by UPDATE statement in RAW plpgsql

This has been asked multiple times here and here, but none of the answers are suitable in my case because I do not want to execute my update statement in a PL/PgSQL function and use GET DIAGNOSTICS integer_var = ROW_COUNT. I have to do this in raw…
anon
21
votes
2 answers

How to check if a row exists in a PostgreSQL stored procedure?

I writing a stored procedure in postgres where I need to check if a row exists then act accordingly. something along the line. IF SELECT * FROM foo WHERE x = 'abc' AND y = 'xyz' THEN -- do something here ELSE -- do something else END; I have…
ams
  • 60,316
  • 68
  • 200
  • 288
20
votes
2 answers

Elegant way of handling PostgreSQL exceptions?

In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following: SELECT * FROM myschema.mytable; I could do the safe-wrapping in the client application: try { result =…
Tregoreg
  • 18,872
  • 15
  • 48
  • 69
1 2 3
…
99 100