Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
421
questions
1
vote
1
answer
30
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 ...
- 315
0
votes
1
answer
39
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
83
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
377
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
74
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
20
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
214
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
45
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
136
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 ...
- 349
0
votes
1
answer
367
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
121
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
272
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
457
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
135
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
414
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
149
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
64
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
210
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('...
- 349
3
votes
1
answer
152
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
101
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,083
0
votes
1
answer
101
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
55
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
537
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
60
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
2k
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
191
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
769
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 ...
- 121
0
votes
2
answers
584
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 ...
- 357
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
161
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
708
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
335
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
414
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
347
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
245
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
305
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
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
302
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
140
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
766
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
496
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
0
votes
1
answer
160
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
2k
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
356
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 ...
- 136
0
votes
1
answer
1k
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,672
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