Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
12 views

Debug RPC Function Creation in Supabase

I've never asked a question before, but I've read what feels like a zillion answers and still can't get my function to work. I'm pretty new to database stuff in general. I'm using Supabase to create ...
Rob's user avatar
  • 1
0 votes
1 answer
35 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( ...
matuco1998's user avatar
1 vote
1 answer
27 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 ...
Anthony O's user avatar
  • 125
0 votes
1 answer
8 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 ...
Boon's user avatar
  • 136
-1 votes
1 answer
39 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 -...
Borislav Zlatanov's user avatar
0 votes
1 answer
37 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 ...
Art Bindu's user avatar
  • 101
2 votes
1 answer
104 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 ...
ceving's user avatar
  • 319
0 votes
1 answer
64 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 ...
eignhpants's user avatar
0 votes
2 answers
62 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. ...
vurqac's user avatar
  • 3
2 votes
1 answer
105 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: ...
Roberto Iglesias's user avatar
0 votes
1 answer
149 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 ...
s.k's user avatar
  • 372
0 votes
1 answer
77 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 $...
James's user avatar
  • 145
3 votes
2 answers
211 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 ...
Firat Oezcan's user avatar
2 votes
1 answer
105 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 ...
Tom's user avatar
  • 428
0 votes
1 answer
58 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 ...
idrizza's user avatar
2 votes
1 answer
206 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('...
ceving's user avatar
  • 319
3 votes
1 answer
89 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 ...
Zaki's user avatar
  • 33
0 votes
1 answer
50 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 ...
vfclists's user avatar
  • 1,053
0 votes
1 answer
79 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, ...
Capacytron's user avatar
1 vote
1 answer
32 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 $...
Vasileios Giannakidis's user avatar
0 votes
2 answers
432 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 ...
Starscream512's user avatar
2 votes
1 answer
57 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 ...
raspi's user avatar
  • 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:...
Gozcab's user avatar
  • 11
0 votes
1 answer
160 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 ...
Sébastien Clément's user avatar
0 votes
0 answers
15 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. ...
Learner's user avatar
  • 99
0 votes
2 answers
562 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 ...
masroore's user avatar
  • 121
0 votes
2 answers
388 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 ...
Leon's user avatar
  • 347
2 votes
2 answers
1k 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 ...
Russell Turner's user avatar
1 vote
1 answer
143 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 ...
DbMajor's user avatar
  • 13
1 vote
2 answers
603 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 ...
Moifek Maiza's user avatar
1 vote
1 answer
237 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 (...
Chris Stryczynski's user avatar
0 votes
1 answer
332 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 &...
Chris Stryczynski's user avatar
0 votes
2 answers
277 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 ...
devins10's user avatar
0 votes
1 answer
219 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, ...
rdbmsNoob's user avatar
  • 459
3 votes
2 answers
289 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 ...
mesr's user avatar
  • 133
0 votes
2 answers
3k 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, ...
rdbmsNoob's user avatar
  • 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 ...
David Krider's user avatar
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 ( ...
user3040157's user avatar
-1 votes
2 answers
206 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=...
user3040157's user avatar
1 vote
1 answer
136 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 ...
eslukas's user avatar
  • 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 ...
Martin Bilbao's user avatar
0 votes
1 answer
699 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,...
Danila Ptuha's user avatar
0 votes
1 answer
406 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 ...
beehive's user avatar
  • 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....
Sevas's user avatar
  • 11
0 votes
1 answer
134 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 ...
Daniil Iaitskov's user avatar
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 ...
J Taylor's user avatar
0 votes
2 answers
325 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 ...
André Carvalho's user avatar
0 votes
1 answer
929 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 ...
tinlyx's user avatar
  • 3,580
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 ...
Topper81's user avatar
0 votes
1 answer
25 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$, $...
Reshmi Nair's user avatar

1
2 3 4 5
9