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.
281
questions
0
votes
1answer
14 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
50 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
36 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
33 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
18 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
53 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
96 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
28 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
56 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
39 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
59 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
23 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
32 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
50 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
719 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
377 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
114 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
35 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
381 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
479 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
202 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
109 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
883 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
193 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
173 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
265 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
98 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
717 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
46 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
57 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
8k 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
141 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
482 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
92 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
164 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
388 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
843 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 ...
