Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
426 questions
2
votes
0
answers
49
views
Postgres - Extreme query planning times on simple query
On a 200mb table fetching entries takes forever due to high query planning times:
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT id FROM tour_bookings WHERE id = 6311251;
| QUERY PLAN ...
- 21
1
vote
2
answers
46
views
How to convert a recursive query to a function?
I have written a PostgreSQL query that's working perfectly:
WITH RECURSIVE x AS (
SELECT i, parent, id, name, type, '' AS path
FROM entry
WHERE name = 'JS-VBNET-2'
UNION ALL
SELECT e.i,...
1
vote
2
answers
44
views
Procedure with nested FOR loop to update rows
I am trying to write a Postgres procedure in pgAdmin. The procedure I've written fails with different error messages depending on where I put the cursor. It even sometimes seems to succeed, but with ...
- 13
0
votes
1
answer
30
views
Why doesn't DISCARD PLANS/ALL work for queries inside PLPGSQL functions? [duplicate]
I'm experiencing unexpected behavior with query plan caching in PostgreSQL when using different function types. I have a dashboard query that exhibits significantly different performance ...
- 203
1
vote
1
answer
132
views
PostgreSQL: pg_dump fails to correctly pg_restore due to missing or erroneous custom function, but present in dump
I am running PostgreSQL 13.15 in a local docker container, with matching pg_dump/pg_restore versions. When dumping and restoring the database, one of the custom functions that was previously present ...
- 50
1
vote
1
answer
35
views
Make sequential groups of rows that surpass a minimum sum
Let's say I have a table with a timestamp (ts) and a value (a).
With some sample data:
ts, a
1, 10
2, 30
3, 10
4, 20
5, 40
6, 10
7, 20
8, 30
etc
I would like to group rows, in ts order, by summing a ...
- 325
0
votes
1
answer
75
views
Locking write to tables while Postgres procedure is executing
I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt ...
- 103
0
votes
1
answer
130
views
Debug RPC Function Creation in Supabase
I'm pretty new to database stuff.
I'm using Supabase to create an application where I keep track of the number of likes on certain items ('clicks'). I want to filter items either by the date the likes ...
- 3
0
votes
1
answer
651
views
"ERROR: Cursor does not exist" in PL/pgSQL procedure
I am trying to transfer a specific amount of film with an specific quantity from one store_id to another. For this I have tried doing a procedure:
CREATE OR REPLACE PROCEDURE transfer_inventory(
...
1
vote
1
answer
125
views
How can I evaluate string-formatted column conditions against an unregistered record?
DO $$
DECLARE
rec RECORD;
outcome BOOLEAN;
rule TEXT;
BEGIN
-- Assume the record references a single row that contains the columns under test in the rule
rule := 'columnA > 30 ...
- 125
0
votes
1
answer
26
views
How to Retrieve and Compare All Settings and Overrides for a Specific Table in PostgreSQL (version 12)?
I am working with a PostgreSQL 12 database and currently investigating a table (table_name in schema schema_name) that has accumulated a significant number of dead tuples. To address this, I am ...
- 136
-1
votes
1
answer
248
views
Query postgres db in a Docker container from another container using peer auth
I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so:
docker run --rm -itd --name=containerone -...
0
votes
1
answer
50
views
postgresql query IllegalArgumentException in Yellowbrick DB [closed]
I'm writing Pl/pgSQL joining query in Yellowbrick DB.
I have join 6 tables at a time. It was working fine.
Suddenly I observe my query is throwing error: "IllegalArgumentException"
Checked ...
- 101
2
votes
1
answer
181
views
How to catch a particular exception?
It is possible to catch an exception in plpgsql:
EXCEPTION WHEN unique_violation THEN
But how to check for a violation of a particular constraint?
The following example creates two keys: one for a ...
- 359
0
votes
1
answer
685
views
Using dbeaver and postgresql can I declare a variable INTERVAL to pass into a sproc?
I have a PostgresSQL table that has a field for TIMESTAMP, and an associated stored procedure that takes an INTERVAL and calculates the difference from now() + in_interval. I want to use dbeaver to ...
- 103
0
votes
2
answers
194
views
Function performance is worse than raw query in PostgreSQL
I am trying to squeeze out the all the performance from my PostgreSQL database, also I want to abstract my query definitions from my application layer.
To do that, I am using table-valued function. ...
- 3
2
votes
1
answer
369
views
Error: type of parameter n (X) does not match that when preparing the plan (Y)
I'm encountering an issue with a PL/pgSQL function where it returns different data types based on a condition, and it's resulting in a type mismatch error. Here's a simplified version of the function:
...
0
votes
1
answer
726
views
Get absolute number and percentage of NULL values for each column of each table in a given schema
I'd like to extract some basic statistics about how tables are populated in a given PostgreSQL 16 schema, such as the absolute number and percentage of null values for each column in all tables in ...
- 384
0
votes
1
answer
198
views
PL/pgSQL syntax error at or near "table"
I've been learning some PL/pgSQL and am having a hard time declaring and using a table type.
I know this script is dumb and doesn't accomplish anything, I'm just trying to learn.
Here is my query:
do $...
- 145
3
votes
2
answers
615
views
Is automatically renaming indexes when renaming columns relevant for database health/maintenance?
I am currently building out a few features for a project where customers are able to change up their database schema via an UI and we handle all the nitty-gritty details on running the required ...
- 33
2
votes
1
answer
175
views
Return state of a row when executing a function in PostgreSQL
For years I have been creating functions that are executed by triggers, and in the function I always returned OLD (for DELETE triggers) or NEW (for INSERT or UPDATE triggers).
Is it mandatory to ...
- 428
0
votes
1
answer
70
views
Split lines against polygons in a PostGIS trigger
I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
- 1
2
votes
1
answer
216
views
How to define a function accepting exactly three characters?
I tried this:
create or replace function c3 (arg char(3))
returns void
language plpgsql as
$$
begin
raise notice 'Value: %', arg;
end;
$$;
But it accepts more than three characters:
select c3('...
- 359
3
votes
1
answer
196
views
Creating VIEW using EXECUTE ignores USING clause parameter?
I am trying to create a UDF that creates a VIEW dynamically using the USING clause feature. The problem is, EXECUTE will not accept the passed parameter into the CREATE VIEW command.
CREATE OR replace ...
- 33
0
votes
1
answer
142
views
Can pl/pgsql function take a variable number of parameters?
I have a database with multiple schemas which have the same tables and views, and I want to create a procedure that creates a view from one the views in the schema.
CREATE OR REPLACE FUNCTION ...
- 1,093
0
votes
1
answer
111
views
User-defined aggregate function to sum jsonb values
I have agg function that sums decimals. Business logic omitted for brevity
CREATE OR REPLACE FUNCTION calculate_snapshot_internal_decimal(_this_value DECIMAL,
...
- 137
1
vote
1
answer
70
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 $...
1
vote
2
answers
647
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
63
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
3k
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
212
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,815
0
votes
2
answers
890
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 ...
- 131
0
votes
2
answers
783
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 ...
- 359
2
votes
2
answers
2k
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
183
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
811
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
395
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
459
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
389
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 ...
- 1
0
votes
1
answer
253
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, ...
- 459
3
votes
2
answers
322
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 ...
- 133
0
votes
2
answers
4k
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, ...
- 459
0
votes
0
answers
81
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 ...
- 67
0
votes
1
answer
30
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
341
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
144
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
3k
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
807
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,...
- 11
0
votes
1
answer
545
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 ...
- 137
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