Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
383
questions
0
votes
0
answers
19
views
Error creating FUNCTION [closed]
I am trying to create a FUNCTION. The sql returns CREATE FUNCTION.
Any pointers why it doesn't create the function, would be appreciated?
The SQL is:
CREATE OR REPLACE FUNCTION update_symbol_changes() ...
0
votes
0
answers
42
views
Avoid infinite loop within a While block
I have one table (Table1)on which I would like to apply a geo process (merge joining geometries on specific criterias). This process imply that all entities that where already merged should be ...
0
votes
1
answer
30
views
PostgreSQL function call error: structure of query does not match function result type
i have function
CREATE FUNCTION create_post(
txt text,
created BIGINT,
photourl text,
user_id bigint
) RETURNS TABLE(
id bigint,
"text" text,
created_at timestamp,...
0
votes
1
answer
22
views
postgresql trigger --> trigger function --> sub trigger function chain
I have a complex trigger function that I wish to break out into multiple sub functions. The trigger would call the master function and the master function would have logic in it to decide which sub ...
- 71
1
vote
1
answer
69
views
Use variable as column name
I'm trying to create trigger in PostgreSQL 14:
create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$
declare
equipment_id int := new....
- 11
0
votes
1
answer
31
views
PL/pgSQL package manager for Postgres libraries
I am a software engineer studying PostgreSQL.
PostgreSQL supports lots of languages (e.g. plpgsql).
I don't see an easy way to install storied procedures.
Is there some sort of pip or npm analog?
I ...
- 103
0
votes
1
answer
36
views
PostgreSQL - Write loop that creates new tables based on select query
In concept this is simple. I'm constructing a new database. I have all of the tables I want to create in tb.table_build and I want to write a statement that creates new tables for each of the items in ...
- 1
0
votes
2
answers
56
views
I'm trying to make an SQL injection in my own function in PostgreSQL 13
Just for learning purposes, I'm trying to create a function using PLPGSQL and make an SQL injection on it. I recently learned about format, USING and quote_literal and quote_indent, so I'm good about ...
- 126
0
votes
1
answer
59
views
PostgreSQL jsonb as hash tables/dictionaries?
PostgreSQL does not seem to have a native data type for dictionaries. The new jsonb (and json) type seems to have the right interface: given a key, a jsonb object returns a value.
I am just wondering ...
- 2,814
0
votes
1
answer
179
views
Dynamic CREATE TABLE AS with indexes
I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process).
One of the things I need to move are a bunch of stored procedures, that generate tables ...
- 35
0
votes
1
answer
18
views
Using tablenames from a config table for joins
SELECT
$model_master_id$,
$model_market_segment$,
$country$,
$ctry_cd$,
$score_run_id$,
$period_id$,
$prediction_from_dt$,
$prediction_to_dt$,
$buy_acq$,
$...
- 31
0
votes
1
answer
257
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 ...
- 2,006
3
votes
1
answer
67
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,'>=');
...
- 395
1
vote
1
answer
65
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 ...
- 113
1
vote
1
answer
145
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 "...
- 124
0
votes
1
answer
264
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 ...
- 117
1
vote
1
answer
190
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 ...
- 13
0
votes
1
answer
262
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
...
- 439
0
votes
1
answer
235
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 ...
- 111
1
vote
1
answer
72
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 ...
- 27
0
votes
1
answer
222
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 ...
- 111
0
votes
1
answer
613
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 ...
- 111
1
vote
1
answer
592
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)...
- 181
1
vote
1
answer
71
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 ...
- 181
2
votes
1
answer
477
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
1
answer
107
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 ...
- 125
0
votes
1
answer
50
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" ...
- 111
1
vote
0
answers
29
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 ...
- 119
1
vote
1
answer
573
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 ...
- 123
0
votes
0
answers
228
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) ...
- 111
1
vote
2
answers
104
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;
...
- 111
0
votes
1
answer
1k
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,
...
- 125
1
vote
2
answers
38
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 ...
- 123
0
votes
0
answers
83
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 ...
- 99
-1
votes
1
answer
77
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 ...
- 113
0
votes
3
answers
54
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 ...
- 36
1
vote
1
answer
353
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 ...
- 11
0
votes
1
answer
102
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 ...
- 301
0
votes
1
answer
37
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 ...
user229251
1
vote
1
answer
536
views
COMMIT works in one plpgsql code block, but in 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
2
answers
711
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 ...
- 37
0
votes
1
answer
264
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
3
answers
1k
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 ...
- 113
1
vote
1
answer
349
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 ...
- 115
2
votes
1
answer
2k
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 ...
- 123
0
votes
2
answers
176
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 ...
- 103
1
vote
1
answer
60
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 ...
- 83
0
votes
2
answers
72
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
3
answers
74
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, ...
- 125
3
votes
3
answers
454
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,...
- 33