4,429 questions
1
vote
1
answer
48
views
Postgresql- trigger to insert and then to truncate table
I'm using a transition table to import rows in a source table after collecting data with Qfield on the transition table.
This function works well to import rows:
CREATE OR REPLACE FUNCTION shema....
- 13
1
vote
1
answer
63
views
Passing array to WHERE IN on multiple columns in a function
I have a table as following:
create table test
(
id varchar(20),
text varchar(20)
);
insert into test values(1, 'a')
insert into test values(2, 'b')
insert into test values(3, 'c')
To check ...
- 137
0
votes
1
answer
40
views
syntax error when creating an if statement on postgresql function
I'm learning postgresql and am trying to create a function. I have code like this:
create or replace function test_function(current_uncertainty float, last_updated timestamp, min_uncertainty float)
...
- 1,660
0
votes
0
answers
24
views
Postgresql: How to catch broken SQL in function [closed]
I am trying to make a simple plpgsql function to catch sql error.
FIXED MY own script.
(notable. Do not use replace function. It will not error correctly if fed broken code to replace non broken ...
- 79
1
vote
1
answer
45
views
Special variable FOUND not set properly?
When assigning my variable _variable, I need to check whether the query returned any rows. "No row" is converted to null, but I want to keep the previous value instead in this case. While an ...
- 375
1
vote
0
answers
37
views
How would you ignore a trigger within another trigger with django-pgtrigger
I am updating tbla."field_2" from a trigger in tblb. Except tbla."field_2" is protected with a pgtrigger.ReadOnly trigger.
This works without the pgtrigger.ReadOnly trigger in TblA....
0
votes
0
answers
49
views
How can I create a dynamic update query in Postgres with plpgsql?
I want to build a query that can dynamically update tables, that is, receive a json with the fields that need to be updated.
I have something like this:
CREATE OR REPLACE FUNCTION ...
- 888
1
vote
1
answer
42
views
plpgsql function with "case when" to update column value [duplicate]
I want to create a plpgsql function that will be called by a BEFORE INSERT trigger.
This function is updating a column based on value from another column.
Here is the code:
CREATE OR REPLACE FUNCTION ...
- 265
2
votes
1
answer
66
views
How to efficiently FOR-loop over a huge SELECT result set in PL/pgSQL
In a PL/pgSQL stored procedure, I would like to calculate multiple averages (simple, weighted, etc) over different periods of time and over different number of data points, all in a single loop.
...
- 1,727
1
vote
3
answers
51
views
Can 2 postgresql functions be called in parallel?
CREATE OR REPLACE FUNCTION wrapperFunction()
RETURNS bytea AS
$BODY$
BEGIN
SELECT function1() || function2();
END;
$BODY$
LANGUAGE plpgsql
Is there a way to make calls to function1 and ...
- 15.1k
0
votes
0
answers
49
views
Why plpython functions increase transaction counter much more then plpgsql functions?
I'm trying to understand why plpython function has much bigger impact on transaction counter in Postgres than plpgSQL function. Below is example which uses 2 functions:
Vesion with plpgSQL (each part ...
- 331
4
votes
4
answers
121
views
Find the statement currently running in my PL/pgSQL code block
Is there a way to figure out which statement from the block is currently running in Postgres? (Even extra extensions or tracing might be an option)
Below is the quick way to reproduce but in real ...
- 367
1
vote
1
answer
89
views
How to declare variables and use them to UPDATE each row based on the preceding row(s)
I'm looping through table1:
create table table1(id,"date",quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'...
- 131
0
votes
2
answers
108
views
Execute dynamic inserts in a procedure
I'm new to Postgres and trying to create code that inserts data from several tables into a single table.
All source tables have a name starting with "B3_HIST_", differing only in terms of ...
1
vote
2
answers
51
views
How to allow mixed integer and numeric parameters in icase
Creatinf icase function using
CREATE OR REPLACE FUNCTION public.icase(
cond1 boolean, res1 anyelement,
cond2 boolean, res2 anyelement,
conddefault anyelement)
RETURNS anyelement LANGUAGE 'sql'...
- 27.7k