Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for the PostgreSQL database system. Questions about PL/pgSQL should probably be tagged "PostgreSQL" as well.

0
votes
1answer
13 views

PL/pgSQL: NEW.field = value; does not work in AFTER UPDATE OF trigger [on hold]

I have the following instruction in my code NEW.field1 = value_for_field1; The trigger, which is defined as following CREATE TRIGGER mytrigger AFTER UPDATE OF field1 ON table1 FOR EACH ROW EXECUTE ...
1
vote
1answer
41 views

Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?

I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists). An example ...
1
vote
1answer
32 views

Syntax error when using exception inside for loop [closed]

I am trying to create the following procedure on postgresql CREATE PROCEDURE run_all_scheduled_tasks() LANGUAGE plpgsql AS $$ DECLARE rec record; BEGIN FOR rec IN SELECT * FROM some_table ...
0
votes
1answer
27 views

Getting the contents of a json file as a variable in plpgsql?

I have a function based this post by Sean Huber in order to get the content of a file: CREATE FUNCTION file_read(file text) RETURNS void AS $$ DECLARE content text; tmp text; BEGIN file := ...
0
votes
1answer
36 views

Unable to wrap a simple CTE in plpgsql loop

For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make ...
0
votes
1answer
33 views

How to eliminate for loop in a nested aggregate problem

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
0
votes
1answer
27 views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0
votes
1answer
22 views

How to use COLUMN and TABLE options in a RAISE statement

According to the docs, there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever ...
0
votes
1answer
53 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
1
vote
1answer
26 views

How to concatenate arrays with user-defined types in pgsql?

Background I am using pgsql to build database tables, and have historically defined the tables in static arrays (eg ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’...
1
vote
1answer
41 views

Surprising results from assigning multiple variables in FOR loop

CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ DECLARE _key text; _value text; BEGIN FOR _key, _value IN ...
2
votes
1answer
229 views

Loop through key/value pairs of a jsonb object in postgresql function

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object. create or replace function myFunction (input jsonb) returns jsonb as $$ BEGIN // ...
1
vote
1answer
210 views

How to insert very big XML data file to PostgreSQL

I'm using PostgreSQL 9.5 and trying to import XML data file to database. To load the XML I use this function from here CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as $$ ...
0
votes
1answer
57 views

Migrating triggers, functions and procedures from Oracle to PostgreSQL

We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written ...
1
vote
1answer
33 views

Sort output of plpgsql function

I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
0
votes
1answer
149 views

How do I reraise an exception in a PL/pgSQL EXCEPTION block?

Consider the following (incomplete) block of PL/pgSQL inside a function: CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC) RETURNS NUMERIC RETURNS NULL ON NULL INPUT ...
1
vote
1answer
36 views

How to get the size of an index created in a plpgsql block

I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block. This works: haki=# create index ix on t(n); CREATE INDEX haki=# select pg_relation_size('ix'); ...
1
vote
1answer
253 views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
2
votes
3answers
119 views

Pass array of mixed type into stored FUNCTION

I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction. the problem here is, that one argument is list of n ...
1
vote
1answer
71 views

How to prepend an element to a JSON array?

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty). How ...
1
vote
1answer
409 views

How to use variables in procedure (not function)? [closed]

As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to: declare a variable select a value into a variable insert the variable into ...
5
votes
1answer
139 views

PostgreSQL UDF (User Defined Functions) overhead

Disclaimer The task may seem esoteric, but nevertheless I want to create a POC of some sorts. The goal My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
-2
votes
1answer
22 views

SQLServer script to Postgres script conversion [closed]

I want to convert the following stored procedure from SQL Server to Postgres This script returns multiple records. Please help me regarding this. CREATE procEDURE [dbo].[...
2
votes
1answer
116 views

COPY query with variable path

I am trying to write a stored procedure that imports a file but what I've noticed is that COPY can be followed by string literal and no variable? I am trying to achieve something like this FUNCTION ...
2
votes
1answer
1k views

Dynamically define a RETURN table (column type, name) for subsequent loop

I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table. https://postgis.net/docs/ST_Intersection.html https://postgis.net/docs/ST_Intersects.html ...
1
vote
2answers
179 views

How to get the column names of a RECORD?

I have a function that declares: DECLARE r RECORD; The data on this record varies and at some point I need to know the column names. How do I get the column names of a RECORD in another record ?
3
votes
1answer
76 views

What is the difference between a SQL function doing a SELECT versus a PLPGSQL function doing a RETURN QUERY SELECT?

Is there a difference between a plain SQL function doing a SQL query: create function get_sports() returns setof sport as $body$ select * from sport; $body$ language sql stable; and PLPGSQL ...
0
votes
2answers
24 views

How do I end function when X

so my function looks like this: create or replace function insert_stuff(a1 text, a2 text, a3 int) returns table (id_i int, id_j int) as $$ begin return query with i1(id1) as select insert_here(a1,a2)...
3
votes
0answers
43 views

Need a Function that allows parameters to pass as argument for logic inside the function in Greenplum

Suppose I have a query :- select 'public.test123 is' || ' ' || quote_literal(obj_description('public.test123'::regclass)) || ';' as test; test ------------------------------------- ...
1
vote
2answers
431 views

How to get max field value length for each TEXT field in all tables in database

I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like: |...
0
votes
2answers
45 views

PLPGSQL function(multiple column output)--gives error

create or replace function help_constraint(p_schemaname text, p_tablename text) returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled ...
1
vote
0answers
22 views

PostgreSQL multiple database dml

I want to insert same data in db_two.tab_emp table automatic by trigger or anything. create database db_one; create table db_one.tab_emp(id int, name text); create database db_two; create table ...
7
votes
3answers
2k views

Concurrent transactions result in race condition with unique constraint on insert

I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
2
votes
1answer
55 views

Return both one value and one column from pl/pgsql function

I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection: one vector containing data whose type is unknown at call time (an array) one string value ...
1
vote
2answers
5k views

POSTGRES declaring variable containing date array in functions

I am trying to declare a date array. I have tried this: DECLARE dateVal DATE[] := ['2018-01-01','2018-02-01']; I get: ERROR: syntax error at or near "[" How do I properly declare it?
0
votes
1answer
121 views

How to get newly inserted row ids when inserting with execute?

I have a function which returns newly inserted row count: BEGIN EXECUTE ( SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1', _tbl, ...
0
votes
2answers
401 views

select json_object() into list postgres function

Here i am trying to get all the select values in a varible. DECLARE ite json; BEGIN select INTO ite from json_object('{1}','{1}'); RETURN ite; when i call this function i got the ...
1
vote
0answers
78 views

Postgresql- how to rotate table partitions, trunc oldest every day?

I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition. I know how to create table partitions on postgres, but I can't understand how ...
2
votes
1answer
145 views

Dealing with out-of-range values on a PARTITION BY RANGE

I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE. There are 5 partitions at the moment, containing a span of a month each, starting ...
0
votes
2answers
2k views

Postgres declare table as a parameter for a function?

I would like to create a function that operates on a table, for example create or replace function test(t table) returns void language plpgsql as $func$ begin select * from t limit 10; ...
0
votes
1answer
359 views

Select date ranges with a 50% overlap to another date range

I have a series of date ranges with columns e.g. start_date, end_date, period_name 1 100 Roman -50 75 Iron Age b -100 20 Iron Age a My team have asked me to ...
1
vote
2answers
1k views

Loop through slices of 2-dimensional array in plpgsql function?

How to loop through an array of arrays in a function and access its elements? Below is the the function (this is only the relevant part). create or replace function public.whatwhatwhy( ...
1
vote
1answer
715 views

How to write validation trigger which works with all isolation levels

Background I need to write validation trigger for a table. Due to nature of validation I can not use builtin integrity checks. For purpose of this question let's say that validation counts number of ...
4
votes
3answers
676 views

Trigger function to update column

I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi. I'm ...
0
votes
1answer
351 views

PostgreSQL: Valid variable names for functions

I cannot find any definitive information on what constitutes a valid variable name in a PostgreSQL function. I am assuming the function is written using plpgsql. I know I can use letters, numerals (...
4
votes
2answers
930 views

How to quote qualified table name with format() function?

I have some problems in properly quoting a table name using the format function in the example below. CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text) RETURNS bool AS $...
1
vote
2answers
50 views

When is SELECT necessary in PL/pgSQL functions?

While assigning the result of a function or expression to a variable in PL/pgSQL, when is it necessary to use a SELECT statement (sub-query)? This may be just my misconception from SQL. I thought ...
9
votes
3answers
2k views

What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

According to the docs: CONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. (...) ... OTHER CONTENTS ... Even with this option ...
2
votes
1answer
78 views

Automate update columns in a view with trigger function in plpgsql

Im attempting to update several columns in a view very similar to this post. The difference is that Im combining in some cases up to 3 tables, and for each update on the view I would like the trigger ...
2
votes
1answer
2k views

ERROR: “sql ” is not a known variable

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running. I'm invoking the cobertura.sql file using ...