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.
296
questions
1
vote
2answers
48 views
How to avoid multiple SELECTs from a bag of properties table?
I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is ...
0
votes
1answer
24 views
Issue of structure of query does not match function result type
"structure of query does not match function result type" this isssue occurred when i try to execute the this function.
CREATE OR REPLACE FUNCTION historicalavg01() RETURNS SETOF text AS
$$
DECLARE ...
2
votes
1answer
23 views
Custom error code class and numbers for Postgres stored functions
I'm writing a collection of stored functions in Postgres 11.5, and want to RAISE EXCEPTION when various pre-conditions are not met. For example, a null or empty string with a string is required, or an ...
-1
votes
1answer
36 views
How to return value from function in PostgreSQL [closed]
I have a function in postgreSQL, I want that if the query in function executes properly then it should return true else false
example:
CREATE OR REPLACE FUNCTION enquiry."fn_queryFunction"(
IN ...
5
votes
3answers
41 views
Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?
I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
1
vote
0answers
11 views
How to get the name of a variable instead of its value in plpgsql?
I write a lot of debugging code in plpgsql along the lines:
RAISE NOTICE 'var1 = %', var1;
(there can be more than one variable to print out).
Is it possible to avoid having to repeat the variable ...
0
votes
2answers
21 views
How to return a single composite type from a plpgsql function without repeating the composite type's parts explicitly as OUT parameters?
I've hunted online and through documentation, but the much more common use case appears to be returning a SETOF some particular row type, not just a single row.
The use case below is trivial for ...
0
votes
1answer
31 views
Blocking drop table for specific schema using trigger
I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas.
CREATE OR REPLACE FUNCTION ...
1
vote
2answers
43 views
Trigger function does not exist, but I am pretty sure it does
I have a procedure:
create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
update sensor
set last_read = read_time
where ...
0
votes
1answer
85 views
plpgsql - stored procedure Comma separated values in parameters
Im writing a stored procedure to get database and table names in variables and then I'll push these values into IN clause.
CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100))
...
1
vote
1answer
27 views
Any ideas what is wrong with this code, Error at or near Insert on a new function
CREATE FUNCTION insert_m_a_k("odOrganizacijaId" uuid, "doOrganizacijaId" uuid) RETURNS boolean AS
$BODY$BEGIN
INSERT INTO mapper_a_k (id, model_a_k_id, organizacija_id, k_a_k, k_a_k_ime, aktiven, ...
1
vote
2answers
286 views
Unable to Create COMMIT inside PostgreSQL 11.5 Procedure
I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.
CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor)
LANGUAGE PLPGSQL
...
2
votes
1answer
262 views
Convert a row to a nested JSON document containing all relations
In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the ...
0
votes
1answer
67 views
Combine SQL query with variable
Redshift support export data to CSV by executing this SQL query.
unload ('select * from bhuvi')
to 's3://bhuvi-bucket/folder//user-data.csv'
iam_role 'arn:aws:iam::1111111:role/myredshiftrole'
...
4
votes
1answer
4k 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
22 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
133 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
67 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
25 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 ...
3
votes
1answer
753 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
62 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
45 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
20 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
91 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
703 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
78 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
154 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
72 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
458 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
27 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
99 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
238 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
86 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
3k 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
753 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
528 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
44 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
2k 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
39 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');
...
2
votes
1answer
1k 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
494 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
242 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 ...
2
votes
1answer
3k 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
527 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
28 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
280 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
4k 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
419 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 ?
5
votes
1answer
289 views
SQL function with SELECT vs. PLPGSQL function with 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)...
