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 ...
