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
1
vote
2 answers

PostgreSQL Hierarchical Relationship Functions

CREATE TABLE sectors ( sector_id integer PRIMARY KEY, sector_name varchar(100) NOT NULL, parent_sector_id integer REFERENCES sectors(sector_id) ); INSERT INTO sectors(sector_id, sector_name, parent_sector_id) SELECT 1, 'All sectors',…
Nick Binnet
  • 1,910
  • 7
  • 33
  • 49
1
vote
1 answer

Postgres: Select multiple columns and compare with array

I would like to do the following (written in some kind of simplified pseudo code) in just one PL/PGSQL function inside a Postgres 12 database: SELECT numcolA, numcolB FROM myitemtable INTO _unused_items_array; FOR EACH _numcol1, _numcol2 IN SELECT…
DevNewBee
  • 11
  • 3
1
vote
1 answer

Convert text in PLPGSQL/Orafce

I'm working with PL/pgSQL and Orafce after a conversion from Oracle. In a function I'm writing in a file. I would like to write with the LATIN1 encoding. My database is in UTF8. There is convert functions but I think I miss something because I don't…
Martin Dejax
  • 11
  • 1
1
vote
1 answer

PostgreSQL: generate all possible strings of arbitrary length from a set of characters

I'm trying to generate all possible strings of length x using a fixed set of characters in PostgreSQL. For the simple case of x = 2 I can use the query below, but I cannot figure out how to do it for an arbitrary length (I'm assuming this will…
Tombaugh
  • 118
  • 8
1
vote
1 answer

How to return custom record with calculated data

I want to return a custom empRecord row. How can I do this? Please do not change the select statement. Essentially I just want to return the name of an employee and a new rank. CREATE TABLE employees ( id integer primary key name text, …
Zoey Malkov
  • 776
  • 6
  • 16
1
vote
1 answer

Using update inside of PL/pgSQL

To begin with, I am a newbie to SQL and PostgreSQL. It might be a silly beginner's mistake. create or replace function temporary_function_for_getting(admission_number_text text,organization_id bigint,user_object_json json) returns…
Ankush Verma
  • 689
  • 1
  • 8
  • 17
1
vote
2 answers

Is it possible to append a WHERE clause in SELECT in plpgsql?

I have a function with several IN params. I need to build a complex select and use the params in the WHERE CLAUSE. Is there any way I can add some conditioning like this: if ($1 > 0) then condition1 ='col1 = $1'; end if; SELECT * from table1…
johnlemon
  • 20,761
  • 42
  • 119
  • 178
1
vote
1 answer

Creating a partial index based on declared variables inside a DO block

I have a table on which I want to have two different partial unique indexes that include unique constraints on different columns, based on a the value of another column in the table (which is a foreign key). Here's an example: id | col1 | col2 |…
mel
  • 95
  • 1
  • 6
1
vote
1 answer

get velue from query PERFORM 1 into temp FROM master_balance where externalkey = 'jknfl12j3' and tenant_record_id = '2' for update;1

I have function, inside the function running postgresql this query : PERFORM 1 into temp FROM master where externalkey = 'jknfl12j3' and tenant = '2' for update; Table structure master : id externalkey tenant balance 1 jknfl12j3…
blinkbink
  • 89
  • 5
1
vote
2 answers

How to execute Anonymous Block PL/pgSQL (PostgreSQL 13) from Npgsql 4.1.5.0 in C#

I have this anonymous block PL/pgSQL: DO $$ DECLARE secuencial INT; BEGIN SELECT MAX("CodigoFactura") + 1 INTO secuencial FROM "Factura"; IF secuencial IS NULL THEN secuencial := 1; END IF; RAISE NOTICE '%',…
Ejrr1085
  • 975
  • 2
  • 16
  • 29
1
vote
1 answer

How to assign multi line text to a string in stored procedure in Redshift

Following code on command line select $$ Line 1 Line 2 Line '3' $$ But when I try to use $$ delimited string for setting a variable in stored proc , it does not work I get error [Amazon](500310) Invalid operation: syntax error at…
user3376169
  • 405
  • 1
  • 5
  • 17
1
vote
2 answers

Declare a Table as a variable in a stored procedure?

I am currently working a stored procedure capable of detecting continuity on a specific set of entries.. The specific set of entries is extracted from a sql query The function takes in two input parameter, first being the table that should be…
kafka
  • 573
  • 1
  • 11
  • 28
1
vote
1 answer

Trigger using adjusted OLD / NEW values

I am trying to put together trigger which does multiple things: before any insert updates automatically columns created_date and created_by for the inserted row and also inserts this row with updated values into archive (history) table before any…
Miro
  • 599
  • 10
  • 29
1
vote
2 answers

PL/pgSQL - Find out which items of an array exist in the table

I am trying to find a way to split a text array into two arrays by their existence in a table. Let's say that the users table has these three IDs: 1, 2, 3, and I have an input_array_ids with values 1, 2, 5. IDs 1, 2 exist in the table so it goes…
Özenç B.
  • 928
  • 3
  • 8
  • 25
1
vote
2 answers

How can wrap-on-overflow math be done in Postgres?

Given the following example function: CREATE OR REPLACE FUNCTION add_max_value(_x BIGINT) RETURNS BIGINT LANGUAGE sql AS $$ SELECT 9223372036854775807 + _x; $$; If this function is called with any positive value, the…
dminuoso
  • 963
  • 1
  • 7
  • 10
1 2 3
…
99 100