Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
401
questions
1
vote
1
answer
15
views
pg_restore drops extension plpgsql
We face a situation where pg_restore drops extension plpgsql and then recreates it. This is done by a CI job we have but i could not reproduce it. These are the commands we use:
pg_dump -d $...
0
votes
2
answers
78
views
(PostgreSQL trigger function) How to use TG_ARGV as column names of NEW and OLD?
I am trying to create a trigger function in PostgreSQL that will take TG_ARGV[0] and TG_ARGV[1] and use those as column names of NEW and OLD, so that I can pass them in as arguments like this:
CREATE ...
2
votes
1
answer
52
views
Moving window search which stops at longest substring matches
I'm trying to create a search which searches a field for a longest matches possible and then stop.
Here's the basic idea in Python for single match:
import re
def generate(txt: str) -> list:
s ...
- 131
1
vote
2
answers
300
views
Why am I getting the "Query has no destination for result" error?
I'm starting on PL/pgSQL, and have written this code to update and return a query, but it isn´t working.
This is the SQL error:
SQL Error [42601]: ERROR: query has no destination for result data
Hint:...
- 11
0
votes
1
answer
70
views
How to use setval into a function?
I created a trigger function to set the value of a sequence in a remote database table, using dblink and setval.
CREATE OR REPLACE FUNCTION my_function () RETURNS TRIGGER AS $$
DECLARE
...
BEGIN
...
- 1,745
0
votes
0
answers
14
views
How to handle continuous upgradable pgsql database
I have a PGSQL database with several tables(20 million records each table and 100+ columns) which need to be updated(appending rows to the existing table) by several thousands rows almost every month. ...
- 99
0
votes
0
answers
16
views
How to run a PL/pgSQL procedure when an user connects to PostgreSQL DB?
I am creating a software for parking slots reservations in parking buildings. One requirement is to whenever an user logs into the app, his Public IP address should be stored along with his user name ...
0
votes
2
answers
134
views
Get the maximum column sizes for each NUMERIC columns in all tables in postgresql
I'm trying to calculate the maximum length of NUMERIC columns in a postgres db. There are a number of tables in the db, and most of those tables contain a number of numeric columns.
I'm importing a ...
- 111
0
votes
2
answers
52
views
How to correctly select multi-columns from a return of a function in Postgresql?
I have hundreds of tables with same structure in a Postgresql-11 db, and I need to perform a
same statistics on each of them.
Since the real statistics logic is very complex, in order to simplify my ...
- 285
2
votes
2
answers
481
views
PostgreSQL: Insert dynamic columns and values from jsonb argument
This question relates to Update dynamic column names using keys from jsonb argument and my code is based on the answer provided there by https://dba.stackexchange.com/users/3684/erwin-brandstetter
I'm ...
1
vote
1
answer
93
views
Return value from WITH clause in PL/pgSQL
I am attempting to RETURN an integer from a plpgsql MERGE-related action that uses WITH. The value I want to RETURN is within the WITH statement below - the id. The error I receive with the below code ...
- 13
1
vote
2
answers
390
views
PL/pgSQL select statement inside trigger returns null using where NEW
I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS ...
- 46
1
vote
1
answer
155
views
A simple example of how to use PLPGSQL?
How can I define a query, and then run the query and a SELECT COUNT(*) (return the number of records) on that query's result?
I want results similar to:
postgres@127:postgres> (SELECT 1) union all (...
0
votes
1
answer
141
views
How do I run / execute PL/pgSQL?
This might seem like a silly question, but how do I "run" PL/pgSQL code?
I tried using psql however I'm getting:
postgres=# exampleTest varchar := 'hello';
ERROR: syntax error at or near &...
0
votes
2
answers
173
views
How to implement "SELECT INTO" in Oracle with PostgreSQL commands?
I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves ...
0
votes
1
answer
175
views
Alter a function in Postgres 12 to allow for dates to be default or populated [duplicate]
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 419
3
votes
2
answers
194
views
How to prevent what looks like PostgreSQL faulty optimization inside a function?
I have defined a function that uses PostgreSQL's COPY facility for reading the output of a shell program which reads its input from stdin and returns a string through stdout. Because of the particular ...
- 33
0
votes
2
answers
1k
views
Default values for function parameters
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 419
0
votes
0
answers
78
views
Deduplicate while updating self-references in the table, and foreign references in another (PostgreSQL)
I've read a few posts here that I've been able to find about this, and they usually involve a comment about how the poster has inherited a mess, and they're trying to clean it up. Unfortunately, I am ...
- 47
0
votes
1
answer
29
views
How to insert "C:\" in text column, PL/pgSQL
I'm currently facing a strange issue on my PL/pgSQL query to insert string in text column, i will explain:
I have a table with following schema:
CREATE TABLE IF NOT EXISTS Loading_Info
(
...
-1
votes
2
answers
115
views
Track row affected by a plpgSQL's DML from Python code block [closed]
I just wanted to know that how could I hold the value of a DML(affected rows) using python code.
Suppose a DML is affecting 10 rows so how will I print it using python.
db = psycopg2.connect(dbname=...
1
vote
1
answer
126
views
Filter a SELECT query with predicates passed as single hstore value
For an API I am building I am supposed to write a function with a single hstore argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an ...
- 111
1
vote
0
answers
2k
views
Postgres - updating millions of rows
I am trying to update a column in a table with around 30 millions of rows without downtime.
To do that, I am not dropping indexes and constraints because I want to keep the table working normally ...
0
votes
1
answer
542
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
214
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 ...
- 123
1
vote
1
answer
2k
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
98
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
1k
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
277
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
631
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 ...
- 3,315
0
votes
1
answer
2k
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
23
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
2k
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,120
4
votes
1
answer
357
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,'>=');
...
- 445
1
vote
1
answer
616
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
871
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 "...
- 154
0
votes
1
answer
1k
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 ...
- 219
2
votes
1
answer
1k
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 ...
- 123
0
votes
1
answer
2k
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
2k
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 ...
- 121
1
vote
1
answer
327
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 ...
- 37
0
votes
1
answer
1k
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 ...
- 121
0
votes
1
answer
3k
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 ...
- 121
1
vote
1
answer
3k
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)...
- 277
2
votes
1
answer
1k
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 ...
- 277
3
votes
1
answer
3k
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
1k
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 ...
- 135
0
votes
1
answer
206
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" ...
- 121
1
vote
0
answers
31
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
4k
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 ...
- 133