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

Column '' has unsupported type "information_schema.sql_identifier"

I am trying to test my stored procedure in MySQL workbench/j. I get an error when I am trying to call the stored procedure. I have created a table to store the result of my stored procedure CREATE TABLE IF NOT EXISTS ableok ( name VARCHAR(50)…
Denise
  • 139
  • 1
  • 2
  • 12
1
vote
1 answer

PLPGSQL parse json IN parameter and compose json OUT parameter

I'm trying to make a stored function on postgres server which could handle json parameters. The server version is 12. It is used as backend server for an android app. The current process of retrieving data from DB is as follows: APP ---(http post…
Vind Iskald
  • 345
  • 3
  • 14
1
vote
2 answers

PostgreSQL - using group by sum as input to function for creating additional column in a view

I am new to PostgreSQL and this might not really be the best way to achieve my result, but here is what i am trying. I have a table called meteo and another called areas. meteo has values of measured precipitation at different stations, and the…
umbe1987
  • 2,894
  • 6
  • 35
  • 63
1
vote
1 answer

How to use variable in view definition in Postgres/plpgsql

I am using plpgsql with Postgres 10.6. I have a function that declares and gives value to a variable. That function also defines a view, and I would like to use the variable within the definition. create view myview as select …
susie derkins
  • 512
  • 2
  • 6
  • 17
1
vote
3 answers

Read binary file with PostgreSQL without pg_read_file

I need a solution to the following problem with a clear PostgreSQL 9.4: Read a zipped file from server into a bytea column No extensions allowed Superuser allowed Postgres user has permission to read the file Can be a user function EDIT: The file…
Luiz Vaz
  • 1,669
  • 1
  • 19
  • 32
1
vote
0 answers

postgraphile - function to add structured data as field on mutation

I'd like to do the simplest possible thing to add structured data to a postgraphile mutation. I have a simple example table: > \d test1.my_table …
Hoopes
  • 3,943
  • 4
  • 44
  • 60
1
vote
1 answer

PostgreSQL custom aggregate: more efficient option than array concatenation

Consider a custom aggregate intended to take the set union of a bunch of arrays: CREATE FUNCTION array_union_step (s ANYARRAY, n ANYARRAY) RETURNS ANYARRAY AS $$ SELECT s || n; $$ LANGUAGE SQL IMMUTABLE LEAKPROOF PARALLEL SAFE; CREATE…
zwol
  • 135,547
  • 38
  • 252
  • 361
1
vote
1 answer

How to create queries dynamically based on filters selected in PostgreSQL?

I have a UI where products of various categories will be listed. There will be filters on various attributes which users can select to filter out the products. How should I create a query which will change dynamically based on the selection of…
Kartikeya
  • 53
  • 1
  • 1
  • 10
1
vote
2 answers

How to return a value from a stored procedure (not function)?

I have a Stored Procedure that inserts, updates or deletes tablerows. It was working fine while all parameters were used as input. However, I need to return the ID of last inserted row. For that I tried using an INOUT parameter and RETURNING after…
Kartikeya
  • 53
  • 1
  • 1
  • 10
1
vote
1 answer

How do variables work in PL/pgSQL functions

I'm new to plpgsql functions. Can anyone explain how this works? CREATE OR REPLACE FUNCTION test1( IN a TEXT, IN b TEXT ) RETURNS void AS $func$ DECLARE cnt INT2; BEGIN SELECT …
drt88
  • 13
  • 2
1
vote
1 answer

find nth position of a specific character in a string in Postgres

I am running Postgres 9.6 I have a list of various class codes. Here is: 'What I have' And 'What I Want' what I have --> what I want. Equip:Mold --> Equip:Mold Raw:Resin:TPA --> Raw:Resin FG --> FG ... My strategy to…
Daniel L. VanDenBosch
  • 2,350
  • 4
  • 35
  • 60
1
vote
2 answers

How to properly format generated routine result of JSON type by using formatJSON(JSONFormat) in jOOQ?

I'm having issue with properly formatting my JSON result when querying generated Routine by using jOOQ code generator. I'm trying to perform SELECT-clause on my get_all_orders() method defined in PL/pgSQL (mentioned in this question) which returns…
NikolaS
  • 503
  • 2
  • 8
  • 20
1
vote
2 answers

Function returning a table for given table and column names

Lets say I have a table named static in two different databases houses and apartments. static table contains the static information of houses such as house_size, no_rooms, pool, spa. static table in houses database has columns like this: pool spa…
Awaish Kumar
  • 537
  • 6
  • 22
1
vote
2 answers

Problem on creating postgres stored procedure | Postgres 12

I'm kinda new on Postgres, so I need your help for this one. postgres=# select version(); version ------------------------------------------------------------ PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit (1…
nstrtm
  • 15
  • 6
1
vote
1 answer

Is there any defined function in Postgres that lets you know what action triggers a trigger?

I want to do a trigger in Postgres that it will update a log. My trigger will fire when an update, insert or delete is done. CREATE TRIGGER tr_usuario BEFORE INSERT OR DELETE OR UPDATE ON public.usuario FOR EACH ROW EXECUTE…
Javier Martinez Barriga
  • 25
  • 4
1 2 3
…
99 100