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
3 votes
1 answer
61 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
28 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,019
0 votes
1 answer
38 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
20 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
151 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
54 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
610 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
98 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
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. ...
Learner's user avatar
  • 99
0 votes
0 answers
26 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 ...
Smooth Researcher's user avatar
0 votes
2 answers
217 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
  • 111
0 votes
2 answers
85 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
  • 305
2 votes
2 answers
757 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
111 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
450 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
185 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
202 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
208 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 ...
Devin William Sumbaluwu's user avatar
0 votes
1 answer
197 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
  • 429
3 votes
2 answers
229 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
  • 33
0 votes
2 answers
2k 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
  • 429
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 ...
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
141 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
128 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
590 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
265 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
  • 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....
Sevas's user avatar
  • 11
0 votes
1 answer
107 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
295 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
710 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,425
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
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$, $...
Reshmi Nair's user avatar
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 ...
igelr's user avatar
  • 2,120
4 votes
1 answer
388 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,'>='); ...
yaugenka's user avatar
  • 445
1 vote
1 answer
667 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 ...
bca's user avatar
  • 113
1 vote
1 answer
972 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 "...
Jakub Holý's user avatar
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 ...
Ουιλιαμ Αρκευα's user avatar
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 ...
fallincode's user avatar
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 ...
Justin's user avatar
  • 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 ...
ennth's user avatar
  • 121
1 vote
1 answer
351 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 ...
user973347's user avatar
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 ...
ennth's user avatar
  • 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 ...
ennth's user avatar
  • 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)...
Héctor's user avatar
  • 287
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 ...
Héctor's user avatar
  • 287
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 ...
guillermo_dangelo's user avatar
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 ...
Sumchans's user avatar
  • 135

1
2 3 4 5
9