Stack Overflow
Stack Overflow

Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.

PL/pgSQL is the default procedural language for PostgreSQL. It can be used to write user defined functions and trigger functions. Since PostgreSQL 9.0, PL/pgSQL is installed by default. It can be used in anonymous code blocks using the DO statement, in functions and in procedures (since Postgres 11).

Reference:

4245 questions
1
vote
2 answers

Concatenate rows in function PostgreSQL

Assume there's a table projects containing project name, location, team id, start and end years. How can I concatenate rows so that the same names would combine the other information into one string? name location team_id start …
J Szum
  • 548
  • 1
  • 4
  • 12
1
vote
1 answer

Redshift Copy from s3 using for loop

I have many files to load in S3. And I have created manifest file at each prefix of the files. for instance, at s3://my-bucket/unit_1 I have files like below. chunk1.csv.gz chunk2.csv.gz chunk3.csv.gz cunkk4.csv.gz unit.manifest so with copy…
Bumhwan Kim
  • 312
  • 1
  • 4
  • 16
1
vote
1 answer

Conditionally copy data from csv file to PostgreSQL table

Is there a way, using PL/pgSQL, to copy data from csv file to PostgreSQL table only if the file exists, something similar to this: BEGIN IF file_exists(filename) THEN COPY table FROM 'filename' DELIMITER ',' CSV HEADER; END IF; END;
MGrudi
  • 13
  • 1
  • 2
1
vote
1 answer

Postgres unnest with points

I'm trying to write a PL/PGSQL function that takes delimited text as input and inserts rows into a table that includes points. I've got a test function that works OK: rowsaz := string_to_array(input,'?'); INSERT INTO test (num1, num2,…
Debaser
  • 427
  • 1
  • 5
  • 17
1
vote
1 answer

redshift procedure with out parameter and dynamic table name as in parameter

I'm trying to make a redshift procedure that will give output on any table like say a row count for example. But I can't figure out how you are supposed to pass in a table name dynamically and get output. I'm trying to do something like…
J.Hammond
  • 251
  • 3
  • 17
1
vote
1 answer

make_date function does not exist in plpgsql

I've got a plpgsql function. I need to take the date 5 days from today, and then divide month into "fives" to takte the start of "last five". The problem is thay make_date does not exist in the posgres version that is used on the server.... create…
mario
  • 186
  • 3
  • 16
1
vote
1 answer

returning a refcursor with error code and message from postgres function

I have a postgres function that returns a refcursor : CREATE OR REPLACE FUNCTION ngfcst.meta_user_preference_error_test_go(--need to add -- inout o_errcode varchar, inout o_errmsg varchar-- i_login character varying, i_pref_type character…
Goutam Sahoo
  • 59
  • 2
  • 9
1
vote
1 answer

I want to create a postgres stored procedure or function that deletes data from 2 tables where data is older than 30 days

CREATE FUNCTION purge_data(integer) RETURNS int LANGUAGE plpgsql AS $$ DECLARE row_count int; Begin DELETE FROM table_a using table_b WHERE table_a.session_id in (select table_b.id from table_b where cast (started as date) < current_date -…
Sharon
  • 11
  • 1
1
vote
1 answer

How to make a string from Array in plpgsql

I've got a result from my function EXECUTE format('SELECT ARRAY (SELECT tvmid from "%s".tvmtable order by tvmid)', operatorName) INTO tvms; that gives mi array of tvms in format {1,2,3}. I need it to have a String divided with , so I'm trying…
mario
  • 186
  • 3
  • 16
1
vote
1 answer

In PostgreSQL what does the CREATE AGGREGATE option SORTOP do?

From the Postgres documentation (https://www.postgresql.org/docs/9.6/sql-createaggregate.html) I find it hard to deduce what the parameter SORTOP does. Is this option only applicable to an ordered-set aggregate? Concretely I'm trying to create an…
Pieter-Jan Lavaerts
  • 11
  • 1
1
vote
2 answers

How do I pass a list of values in postgres function without using array

I need to create a function where the input parameter is a list of values with a single column: Column1 Abc Xyz Mno Create or replace function dummy(my_list ) returns refcursor as ....... $$ Open ref for…
Goutam Sahoo
  • 59
  • 2
  • 9
1
vote
1 answer

Update Null columns to Zero dynamically in Redshift

Here is the code in SAS, It finds the numeric columns with blank and replace with 0's DATA dummy_table; SET dummy_table; ARRAY DUMMY _NUMERIC_; DO OVER DUMMY; IF DUMMY=. THEN DUMMY=0; END; RUN; I am trying to replicate this in Redshift, here is…
Tpk43
  • 363
  • 1
  • 5
  • 23
1
vote
2 answers

How to apply a function to each element of a json array?

Given this function: CREATE OR REPLACE FUNCTION extract_key( i_m JSONB, i_key TEXT ) RETURNS JSONB AS $$ BEGIN RETURN i_m -> i_key; END; $$ LANGUAGE 'plpgsql'; I'd like to write a function extract_key_array where: SELECT…
zcaudate
  • 13,998
  • 7
  • 64
  • 124
1
vote
1 answer

Why is my Postgres trigger selecting the first row's value and copying it into the rest entries?

I am working on PostgreSQL trigger and I am new to it, I want to develop a Postgres trigger that on insert of the date of birth (DOB), calculates the age, and inserts that date with the corresponding DOB field in the same table. below is my…
kp singh
  • 176
  • 8
1
vote
1 answer

SQL - Dynamic date column with min, max values

I have a table in PostgreSQL(12) like below. |Name|ts |v1 |v2 |v3 | |----|------------------|---|---|---| |aaa |2020-02-15 0:00:00|10 |150|5 | |bbb |2020-02-15 0:00:00|20 |160|10 | |aaa |2020-02-15 1:00:00|30 |170|15 | |bbb…
TheDataGuy
  • 2,712
  • 6
  • 37
  • 89
1 2 3
…
99 100