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
1 answer

how to see properties available for access with NEW plpgsql

Say I have a PLPGSQL function CREATE OR REPLACE FUNCTION function_name RETURNS TRIGGER AS ... BEGIN PERFORM 1 FROM table1 t1 JOIN table2 t2 USING( column_name ) WHERE t1.column_name = NEW.column_name; RETURN…
Scott
  • 121
  • 1
  • 2
  • 9
1
vote
2 answers

Postgres IN operator raises ERROR: syntax error at or near array_variable

I am making function and one of my feature is make use of SQL IN operator. Basically I would like query like this select name from atlas_ins_th_travel_place pp where pp.name IN ('Guyana', 'Mexico'); Then I make function and accept varchar[] as an…
joe
  • 8,383
  • 13
  • 61
  • 109
1
vote
2 answers

How to truncate double precision value in PostgreSQL by keeping exactly first two decimals?

I'm trying to truncate double precision value when I'm build json using json_build_object() function in PostgreSQL 11.8 but with no luck. To be more precise I'm trying to truncate 19.9899999999999984 number to ONLY two decimals but making sure it…
NikolaS
  • 503
  • 2
  • 8
  • 20
1
vote
1 answer

Using ST_Distance in a table

I have a table with id, x ,y value ,I need to find the distance between two points using a function in posgresql. CREATE OR REPLACE FUNCTION distances(lat1 float, lon1 float, lat2 float, lon2 float) RETURNS float AS $dist$ BEGIN …
Joel Abraham
  • 67
  • 7
1
vote
1 answer

Call a Postgresql procedure with a ROWTYPE or RECORD literal

I'm building on a recipe book application. I'm only doing it to practice working in Postgresql: postgres=# select version(); version …
Mike
  • 522
  • 2
  • 14
1
vote
1 answer

Concat setof bytea into single bytea

I have a plpgsql function in Postgres 12 that returns SETOF bytea, which is then made into a single bytea on the server: CREATE OR REPLACE FUNCTION public.get_tile(z integer, x integer, y integer) RETURNS SETOF bytea LANGUAGE plpgsql STABLE…
Avocado
  • 871
  • 6
  • 23
1
vote
1 answer

How do I use a WITH in combination with a FOR loop in PLPGSQL?

I want to create a stored function whose body will loop over the results of a WITH statement. Is that possible? So far I'm getting syntax errors on FOR which follows WITH. Minimal example: DO $$ DECLARE loop_col1 RECORD; BEGIN WITH temp_table…
Arthur
  • 193
  • 1
  • 9
1
vote
1 answer

Order of cascaded deletes in postgres

I encountered a problem which makes me suspect Postgres deletes rows from dependent tables (ON DELETE CASCADE) only after it already deleted the original row. I have these tables: CREATE TABLE IF NOT EXISTS function ( id UUID PRIMARY KEY, …
Ruben Lemiengre
  • 104
  • 8
1
vote
2 answers

How do I write a function in plpgsql that compares a date with a timestamp without time zone?

I want to write a function that returns a table with all the rows between firstDate and lastDate. The rows have datatype timestamp without time zone They also have to be of a specific node id. This is my function: CREATE OR REPLACE FUNCTION…
Jochem
  • 31
  • 6
1
vote
1 answer

Is it possible to call a Postgres SQL script with '\i' but referencing the file as a string variable?

I'm trying to invoke a SQL script within another SQL script using pgsql. I already saw that I can use \i 'path/to/file.sql' where path/to/files.sql is between single quotes. I was trying to replace 'path/to/file.sql' with a variable instead,…
Caco
  • 1,601
  • 1
  • 26
  • 53
1
vote
3 answers

EXCEPTION WHEN OTHERS THEN NULL in Postgres

I have an exception in Oracle PL/SQL that needs to be ported to PostgreSQL pl/pgsql. Below is the oracle variant EXCEPTION WHEN OTHERS THEN NULL; What is the PL/PGSQL variant?
CoderBehindCode
  • 33
  • 1
  • 2
  • 8
1
vote
2 answers

syntax Error in PostgreSQL when I try to create Trigger

I want to create trigger in PostgreSQL. Logic is very simple. I need trigger, if published_at updated and written_at is null, set published_at to written_at. I wrote this one, but it failed. Does anybody have an idea? CREATE function setWrittenAt()…
sdk
  • 73
  • 2
  • 9
1
vote
1 answer

Find the longest concecutive duration of each room_id per weekday

I am trying to find the longest concecutive duration of each room_id per weekday. I want to convert the weekday to its integer representation (0 for Sunday, 1 for Monday etc). What I have so far: create or replace function iweekday(weekday …
Anastasia Kar
  • 33
  • 1
  • 5
1
vote
1 answer

Postgres call function/procedure which executes query & then deletes

I'm looking for a way to have a function or procedure in Postgresql 12, which can be called with a single line for usage in commandline. 1) Perform a query that takes a parameter as input For example: select id from table1 where name = …
Bart Schelstraete
  • 11
  • 3
1
vote
1 answer

Transactions with IF statements

I'm trying to create a transaction that checks if a customer exists, if not, insert the customer. Then update in flight bookings where records reference this customer: BEGIN; IF NOT EXISTS (SELECT CustomerID FROM LeadCustomer WHERE CustomerID =…
user13413898
1 2 3
…
99 100