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.

Filter by
Sorted by
Tagged with
3
votes
1answer
46 views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
1answer
15 views

Trigger on PostGIS line layer occurs twice

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
0
votes
1answer
32 views

POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works ...
3
votes
1answer
53 views

Get base table from materialized view in postgresql

I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i ...
1
vote
1answer
15 views

Using plpgsql to remove all functions

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there # use pg_dump and ...
2
votes
1answer
50 views

How to pass variable to PL/pgSQL code from the command line?

I am running a psql script from the command line with variables something like: psql ...... -v now_utc=$NOW_UTC Then I want to use this in my script like: $$ DECLARE _now_date timestamp := :...
1
vote
1answer
36 views

In PLPGSQL assign a variable based on result of SQL function

I have a PLPGSQL function which receives an address in two parts: num = '123' location = 'main street, anytown, KS' To utilize the various portions of location I found there is a SQL function ...
0
votes
0answers
25 views

How to execute pl/pgsql do block in a bash script? [duplicate]

I want to migrate my databases to a new server, and some of them contain views, which I don't want to migrate. So I wrote a bash script that roughly looks like this # Some commands to migrate the ...
0
votes
1answer
19 views

how can I divide log files for specific DB queries?

Currently my db has a couple of triggers for some (not all) tables that get executed when a specific number of columns has been updated. I keep track if the triggers for those rows on a boolean column ...
1
vote
1answer
55 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
176 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
30 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
69 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
42 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
97 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
24 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
61 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
50 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
56 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 ...
3
votes
1answer
1k 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
420 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
158 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
36 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 ...
2
votes
1answer
575 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
37 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
601 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
264 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
140 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
1k 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
249 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
187 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
2k 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
290 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
117 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
44 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
856 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
47 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
58 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 ...
2
votes
2answers
9k 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
152 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
532 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 ...
2
votes
0answers
103 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
176 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 ...
1
vote
2answers
3k 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
414 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 ...
2
votes
2answers
2k 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( ...