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
74
votes
4 answers

How to iterate over results of query

I am creating a function in pgsql script language, and what I want to do in this point is iterate over the results of a query and for each row do something specific. My current try is the following, where temprow is declared as temprow…
Noob Doob
  • 1,757
  • 3
  • 19
  • 27
74
votes
2 answers

PostgreSQL: How to DROP FUNCTION IF EXISTS without specifying parameters?

I can successfully create a function as follows: CREATE FUNCTION Foo(MY_Value INT) RETURNS INT AS 'SELECT 2 + MY_Value' LANGUAGE SQL However, if I first want to check if the function exists and then drop it if I does, I must specify the…
Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
70
votes
2 answers

PL/pgSQL checking if a row exists

I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists. Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced with PL/pgSQL enough yet to know the best way of…
nnyby
  • 4,748
  • 10
  • 49
  • 105
68
votes
1 answer

Loop over array dimension in plpgsql

In plpgsql, I want to get the array contents one by one from a two dimension array. DECLARE m varchar[]; arr varchar[][] := array[['key1','val1'],['key2','val2']]; BEGIN for m in select arr LOOP raise NOTICE '%',m; END LOOP; END; But…
blue01
  • 2,035
  • 2
  • 23
  • 38
67
votes
3 answers

Debug PostgreSQL function using pgAdmin

I refer this to enable the debugger in the PostgreSQL server in order to debugging the plpgsql function by stepping through the code using pgadmin. I have already set shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' in the…
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
67
votes
1 answer

Iterating over integer[] in PL/pgSQL

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this: declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual…
Dipro Sen
  • 4,350
  • 13
  • 37
  • 50
66
votes
1 answer

Postgres SELECT ... FOR UPDATE in functions

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function: Does it matter which columns I select? Do they have any relation to what data I need to lock and then update? SELECT * FROM table WHERE x=y FOR…
Dan Taylor
  • 677
  • 1
  • 5
  • 6
66
votes
5 answers

update if row with some unique value exists, else insert

I have a URLs table. They contain (id int primary key, url character varying unique, content character varying, last analyzed date). I want to create trigger or something(rule may be), so each time i make insert from my java program, it updates…
Roman Lebedev
  • 903
  • 1
  • 6
  • 16
63
votes
3 answers

How can I execute pl/pgsql code without creating a function?

With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I've begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic…
anon
60
votes
2 answers

SQL Sub queries in check constraint

Can I make SQL sub queries in Check constraint ? I've a post table with columns id, owner I've another table action with columns user_id, post_id Table user with columns id post_id -> post.id and user_id -> user.id also post.owner -> user.id Now I…
Dipro Sen
  • 4,350
  • 13
  • 37
  • 50
59
votes
7 answers

DROP FUNCTION without knowing the number/type of parameters?

I keep all my functions in a text file with 'CREATE OR REPLACE FUNCTION somefunction'. So if I add or change some function I just feed the file to psql. Now if I add or remove parameters to an existing function, it creates an overload with the same…
Steinthor.palsson
  • 6,286
  • 13
  • 44
  • 51
56
votes
4 answers

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work? create or replace function get_user_by_username( username varchar(250), online boolean ) returns setof…
Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
55
votes
5 answers

Hashing a String to a Numeric Value in PostgreSQL

I need to Convert Strings stored in my Database to a Numeric value. Result can be Integer (preferred) or Bigint. This conversion is to be done at Database side in a PL/pgSQL function. Can someone please point me to some algorithm or any API's that…
Salman A. Kagzi
  • 3,833
  • 13
  • 45
  • 64
51
votes
4 answers

The forgotten assignment operator "=" and the commonplace ":="

The documentation for PL/pgSQL says, that declaration and assignment to variables is done with :=. But a simple, shorter and more modern (see footnote) = seems to work as expected: CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$ DECLARE …
A.H.
  • 63,967
  • 15
  • 92
  • 126
50
votes
4 answers

PostgreSQL Exception Handling

I am new to PostgreSQL. Could anybody please correct this query. BEGIN TRANSACTION; BEGIN; CREATE TABLE "Logs"."Events" ( EventId BIGSERIAL NOT NULL PRIMARY KEY, PrimaryKeyId bigint NOT NULL, EventDateTime date NOT…
Nick Binnet
  • 1,910
  • 7
  • 33
  • 49
1
2
3
…
99 100