Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
374
questions
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 ...
