Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.

Filter by
Sorted by
Tagged with
0
votes
1answer
40 views

PostgreSQL: How to switch database inside cursor script

I want to write function for postgresql 10 to grant read-only access to on all databases for the specified user (considerably should be run from superuser) But I have difficulties in database ...
3
votes
1answer
29 views

Evaluate comparison operator from table column

Is it possible to expand a field value into a comparison operator? Something like this: create table math ( value1 int, value2 int, operator text ); insert into math values(1,2,'>='); ...
1
vote
1answer
26 views

Postgres before insert trigger access field of a type

In the DB, I have a type and a table that one of it's rows is that type, such as this: CREATE TYPE state (last_active timestamp, ...) CREATE TABLE devices ( state state, ... ) For any interaction ...
1
vote
1answer
38 views

How to parametrize DDL in a plpgsql procedure?

How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
0
votes
1answer
94 views

Can PostgreSQL save an image to file system?

I'm using Postgraphile to interact with PostgreSQL, and I need upload images to server from my Android app. I plan to send an image as a base64 encoded string by using a mutation, this mutation is a ...
1
vote
1answer
53 views

Column name as an argument of the function called in the trigger (PostgreSQL)

I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue. Take a look at the following code. The bottom line is that I have a function that updates the ...
0
votes
1answer
54 views

PostgreSQL trigger to update date modified [closed]

I am attempting to update the column modified whenever any of the fields are updated in the table users with a trigger in PostgreSQL version 13. CREATE FUNCTION myschema.on_users_update() RETURNS ...
0
votes
1answer
40 views

How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?

I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
1
vote
1answer
44 views

Variadic functions and composite types

I have a function like so: update_stuff_variadic( VARIADIC _stuff_array stuff[] ) I understand that this syntax requires _stuff_array to match the column structure of the stuff[] table. Instead ...
0
votes
1answer
48 views

How to deal with 'failed' pg_cron jobs (postgres)

I have some Jobs (postgres plpgsql procedures) that I am scheduling using pg_cron library/extension and cron expressions. I have some scripts that have failed and thus logged the 'failed' status ...
0
votes
0answers
156 views

Runnig pg_cron jobs on different database and non-public schema says "No procedure matches the given name and argument types"

I'm running pg_cron jobs that of course runs from the default postgres db. I have some functions/procedures that I created in another database called test, and have ran assigned these pg_cron jobs to ...
1
vote
1answer
69 views

SELECT rows from table one by one, comparing one column values with values in an array

I have an array of timestamps in a PLPGSQL code block: timestamp_array. For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
1
vote
1answer
38 views

PLPGSQL store columns of a 2D query output into array variables

In some step of a plpgsql function I need to store a 2D query result into array variables. The following code does the job for scalars (but fails with arrays): SELECT col_a, col_b FROM my_table WHERE ...
1
vote
1answer
81 views

Escape % inside FORMAT() function in Postgres

I'm trying to escape a '%' character inside a format() function in PostgreSQL. The function replaces columns values based on a list of columns, deleting returns (\n) and trimming the strings. CREATE ...
1
vote
1answer
53 views

Insert values to table only if first Insert was successful

Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful. Below is the table I want to ...
0
votes
1answer
22 views

Deleting records from two-child tables in parallel (concurrently)

I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" ...
1
vote
0answers
24 views

List Player Record Types, First Score, and Last Score

I have a record_types and a record table: CREATE TABLE record_types( id SERIAL PRIMARY KEY, record_type VARCHAR(50) ); INSERT INTO record_types(record_type) VALUES ('dribble'); INSERT INTO ...
1
vote
1answer
204 views

How to use user defined exception - PostgreSQL Function

Can you please provide syntax for to use user defined exception in PostgreSQL function? Suppose I want to handle below user defined exception. SQL Error [22023]: ERROR: password is too short. There ...
0
votes
0answers
150 views

How to run pg_cron JOB immediately after another JOB?

TL;DR For anyone familiar with Java, basically I want to run the first few Jobs concurrently (as I understand it, this happens if I launch multiple Jobs separately using different background_workers) ...
1
vote
2answers
51 views

SELECT... with LIMIIT, but iterate forward getting other records?

I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g. $$ DECLARE BEGIN LOOP exit when get diagnostics n_rec_count = 0; ...
0
votes
1answer
532 views

How to insert a row from JSON in Postgres

I have 2 tables recording company market data. A table recording stock market symbols CREATE TABLE appl.symbols ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, symbol VARCHAR(10) NOT NULL, ...
1
vote
2answers
28 views

Viewing the statement in EXECUTE with USING

Let's say with the below function, I need to debug what is executed inside the EXECUTE statement. (a long, dynamic statement with dynamic variables provided in USING in reality) CREATE OR REPLACE ...
0
votes
0answers
57 views

how to alter all tables at once on postgresql for rebuilding sequences & indexes on a same-named column

first, some context might be useful : psql (PostgreSQL) 13.2 (Debian 13.2-1) I made both a pg_dumpall and pg_dump type backups this morning, so I can modify many things. I'm not new to sql but I am ...
-1
votes
1answer
62 views

Optimize postgres function

I have following function which I want to use to update status and distance columns. CREATE OR REPLACE FUNCTION public.check_deviation_table( ) RETURNS void LANGUAGE 'plpgsql' COST ...
0
votes
3answers
31 views

How to return a number surrounded with parenthesis as a negative number

I have a table CREATE TABLE tab1 (col1 integer, col2 character varying(20)); Col2 contains numeric value but the negative values corresponds with parenthesis not and minus (-). This is the sole ...
0
votes
0answers
22 views

why does this DO block have “syntax error near declare” in pgagent?

I have the following code: DROP TABLE IF EXISTS pltest; CREATE TABLE pltest (x jsonb); DO $$ DECLARE startdate text := to_char(current_date - 1, 'YYYYMMDD'); BEGIN EXECUTE format( 'COPY ...
1
vote
1answer
83 views

Using parameters in stored procedure with plpgsql

I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs: Error is "SQL state: 42601", syntax error at "$1". Why ...
0
votes
1answer
67 views

Define recursive composite types

Is it possible to define recursive composite types in Postgres 13? create type "t" as ( "a" int, "b" t ); I am aware that I can use a table with a parent-child ...
0
votes
1answer
34 views

Trigger to prevent column value from A to B, and from B to A

I'm trying to create a trigger that prevents a column to be updated as follows: If column value is "A", it cannot be updated to "B", and if column value is "B", it cannot ...
1
vote
1answer
147 views

Using a commit inside plpgsql block works in one case not another

Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
0
votes
2answers
480 views

How to make a function in postgresql with two parameters to return count from column table?

I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year. The table is something like this: CREATE TABLE orders ( id INT NOT ...
0
votes
1answer
144 views

Get latest timestamp from column in given table dynamically

I want to get last timestmap from a table with the column updated in PostgreSQL. And I want a function to do that. So I can pass the table name as parameter and the function returns the latest ...
1
vote
3answers
688 views

Postgresql: How to pass an array to trigger function in TG_ARGV?

I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be ...
1
vote
1answer
141 views

How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?

I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within ...
2
votes
1answer
1k views

Create a temp table, loop, add data, select from it? "Query has no destination for result data" [closed]

I am creating a temp table, and am fetching data from multiple tables and inserting data into it. When I try to get the data back from the table I get an error: [42601] ERROR: query has no ...
0
votes
2answers
72 views

Any way to verify user password from PL/PGSQL?

I need to verify user password, but this user may be "local" (with md5 method in pg_hba.conf) or LDAP user. This works fine and "local" and LDAP users can login, but I need to ...
1
vote
1answer
21 views

How to create an aggregate to find min and max value of an integer column and return output in text format?

I have a function where I am specifying the table and column but I require an aggregate that can work for any table and column for similar output create function min_max_val() returns text LANGUAGE ...
0
votes
2answers
45 views

Problem with postgres Function

I wrote the following function to query data from tables and columns that I give as arguments: create or replace function field_summaries(gap interval , _tbl anyelement, _col text) ...
1
vote
3answers
50 views

Is there a CASE LIKE in pl/pgsql? [closed]

I'm trying to write a dynamic after trigger function that does an update based on the TG_TABLE_NAME, but it seems that plpgsql's CASE cannot do "LIKE". Is it called differently in plpgsql, ...
2
votes
3answers
260 views

PostgreSQL: How do I stop PostgreSQL from generating logs for PL/pgSQL functions?

I've defined some PL/pgSQL functions which get invoked from the client (e.g. psql, but could be JDBC/Hibernate as well), which is connected using a non super user role. e.g. SELECT chk_pwd('(some.user,...
0
votes
1answer
46 views

What is the benefit of marking function as IMMUTABLE? [duplicate]

I was looking through some of the plpgsql functions in our codebase and realized that most of them cannot be set IMMUTABLE only because they contain reference to now(), e.g. CREATE OR REPLACE FUNCTION ...
1
vote
1answer
16 views

Why does this scalar function not raise an error?

My understanding is that scalar functions always return a row, but I don't understand why the first of these functions does not raise an exception while the second does. The only difference is the ...
0
votes
0answers
16 views

does postgres deadlock if query update and insert in a table inside a function begin? plpgsql

does postgres deadlock if query update and insert in a table inside a function begin? plpgsql function Declare select same table begin update multiple rows same table insert row same table
-1
votes
1answer
39 views

if multiple users run a query that creates a temp table at the same time, would the data in the table be corrupted in each session? [closed]

I am using postgres12. I have a query that creates a temp table named table_x and returns it. I am wondering if this data will be corrupted since there is multiple users running the same query at the ...
0
votes
1answer
225 views

Using function is much slower than running query

I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this : CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$ DECLARE q ...
0
votes
1answer
15 views

ERROR: column "FLAT" does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger

CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp() RETURNS trigger AS $BODY$ BEGIN n.FLAT := UPPER(n.FLAT); RETURN NEW; end $BODY$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER ...
2
votes
1answer
859 views

Find tables with columns with empty and NULL values in Postgresql

After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or ...
0
votes
2answers
125 views

Should Postgres function return/coerce numeric scale/precision?

The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function ...
2
votes
1answer
372 views

PostgreSQL's record type returning null?

I don't understand why this result is possible. Can someone explain why the first assert returns true and the second false? generate_token() is a function that generates a random bytea and inserts it ...
3
votes
2answers
552 views

Case insensitive column names in a postgresql trigger function

I have a trigger function in PostgreSQL 12 which executes something like this: CREATE OR REPLACE FUNCTION "my_latlon_function"() RETURNS trigger AS $$ BEGIN latcolumn:= ( SELECT ...

1
2 3 4 5
8