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

1 2 3 4 5 6