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
216
votes
14 answers

Truncating all tables in a Postgres database

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL? At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute: SELECT 'TRUNCATE…
Sig
  • 4,988
  • 3
  • 28
  • 29
201
votes
7 answers

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …
Sathish
  • 4,403
  • 7
  • 31
  • 53
162
votes
2 answers

How to return result of a SELECT inside a function in PostgreSQL?

I have this function in PostgreSQL, but I don't know how to return the result of the query: CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER) RETURNS SETOF RECORD AS $$ BEGIN SELECT text, count(*), 100 / maxTokens * count(*) FROM…
Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199
159
votes
4 answers

How to create function that returns nothing

I want to write a function with pl/pgsql. I'm using PostgresEnterprise Manager v3 and using shell to make a function, but in the shell I must define return type. If I don't define the return type, I'm not able to create a function. How can create a…
Kabi
  • 1,905
  • 5
  • 20
  • 22
146
votes
2 answers

What are '$$' used for in PL/pgSQL

Being completely new to PL/pgSQL , what is the meaning of double dollar signs in this function: CREATE OR REPLACE FUNCTION check_phone_number(text) RETURNS boolean AS $$ BEGIN IF NOT $1 ~ e'^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$' THEN RAISE…
vector
  • 7,334
  • 8
  • 52
  • 80
132
votes
6 answers

Store select query's output in one array in postgres

My code is: SELECT column_name FROM information.SCHEMA.columns WHERE table_name = 'aean' It returns column names of table aean. Now I have declared an array: DECLARE colnames text[] How can I store select's output in colnames array. Is there any…
mitesh
  • 1,387
  • 2
  • 8
  • 12
125
votes
4 answers

PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);
Vladimir Tsukanov
  • 4,269
  • 8
  • 28
  • 34
112
votes
6 answers

Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the tool I have. This is what I have so…
user2840106
  • 1,121
  • 2
  • 7
  • 3
112
votes
8 answers

Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select * from quote_ident($1) where…
John Doe
  • 9,414
  • 13
  • 50
  • 69
103
votes
7 answers

Return multiple fields as a record in PostgreSQL with PL/pgSQL

I am writing a SP, using PL/pgSQL. I want to return a record, comprised of fields from several different tables. Could look something like this: CREATE OR REPLACE FUNCTION get_object_fields(name text) RETURNS RECORD AS $$ BEGIN -- fetch fields…
skyeagle
  • 3,211
  • 11
  • 39
  • 41
96
votes
3 answers

Difference between language sql and language plpgsql in PostgreSQL functions

Am very new in Database development so I have some doubts regarding my following example: Function f1() - language sql create or replace function f1(istr varchar) returns text as $$ select 'hello! '::varchar || istr; $$ language sql; Function…
user3814846
86
votes
3 answers

How to write function for optional parameters in postgresql?

My requirement is write optional parameters to a function.Parameters are optional sometimes i will add or i will not pass parameters to function.Can anyone help me how to write function. I am writing like select * from test where field3 in…
indu
  • 1,057
  • 2
  • 9
  • 15
85
votes
9 answers

How do I do large non-blocking updates in PostgreSQL?

I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written to or read during the update. I…
S D
82
votes
7 answers

PostgreSQL 9.1 pg_restore error regarding PLPGSQL

I am using Postgres for a django project and I am currently implementing a database backup/restore system that as simple as possible performs a pg_dump when the user clicks backup and then pg_restore when they click restore backup. All seems fine…
fury-s12
  • 1,240
  • 3
  • 12
  • 18
79
votes
10 answers

How do I get the primary key(s) of a table from Postgres via plpgsql?

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function?
jsight
  • 27,819
  • 25
  • 107
  • 140
1
2 3
…
99 100