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
2
votes
1answer
1k 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 ...
3
votes
2answers
7k views

PostgreSQL use NEW in query for INSTEAD OF trigger

I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario: CREATE TABLE Product ( product_id ...
1
vote
1answer
38 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'); ...
8
votes
2answers
3k views

PostgreSQL Upsert not working on Partitioned Table

Have a table like this: CREATE TABLE aggregated_master ( "user" BIGINT, type TEXT, date TIMESTAMP, operations BIGINT, amount NUMERIC, PRIMARY KEY ( "user", ...
2
votes
1answer
2k views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
2
votes
3answers
356 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
946 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(...
1
vote
1answer
194 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
924 views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $...
9
votes
3answers
21k views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error....
1
vote
1answer
2k 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
380 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
3k 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 ...
-2
votes
1answer
25 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
226 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 ...
1
vote
2answers
331 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
165 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)...
0
votes
2answers
49 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 ...
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
1k 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: |...
1
vote
0answers
23 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
3k 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 ...
1
vote
1answer
4k views

Difference between return next and return record

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
30
votes
1answer
18k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
4
votes
2answers
1k 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 $...
2
votes
2answers
13k 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
162 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
661 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
131 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 ...
3
votes
1answer
2k views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...
2
votes
1answer
221 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
482 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
3answers
11k views

Looping with array of values in PL/pgSQL

Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE? FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', ...
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( ...
2
votes
1answer
84 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 ...
5
votes
2answers
1k views

PL/pgSQL regclass quoting of table named like keyword

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
3
votes
0answers
256 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
1
vote
1answer
119 views

Can a plpgsql stored procedure monitor for a query and kill it?

I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which ...
1
vote
1answer
1k 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
961 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
616 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 (...
12
votes
3answers
3k 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 ...
11
votes
1answer
16k views

Error: set_valued function called in context that cannot accept a set. What is it about?

I use Postgresql 9.1, with ubuntu 12.04. Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
0
votes
0answers
42 views

How Create a directory dynamically and output a json file to the directory with PL/pgsql?

I want to export updated records to json files. For this purpose I defined a trigger as follow: CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $BODY$ DECLARE directory text := 'dblogs/me....
1
vote
1answer
902 views

How can one create a directory dynamically in PL/pgSQL?

I want to create a directory in PL/pgsql: CREATE OR REPLACE FUNCTION getNextId() RETURNS VARCHAR(200) AS $BODY$ DECLARE mfId integer; rootDir varchar(50) := 'mfdata/'; createDir text := '...
37
votes
5answers
124k views

How to insert (file) data into a PostgreSQL bytea column?

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
10
votes
2answers
21k views

Return a record with PL/pgSQL function - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...