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
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() ...
user avatar
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 ...
user avatar
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,...
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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$, $...
user avatar
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 ...
user avatar
  • 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,'>='); ...
user avatar
  • 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 ...
user avatar
  • 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 "...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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" ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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) ...
user avatar
  • 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; ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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) ...
user avatar
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, ...
user avatar
  • 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,...
user avatar

1
2 3 4 5
8