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
1 answer

Any tricks to fix this syntax error in PLPGSQL?

I am creating a function to add roles. This function receives two arguments: name and a list of functions that can be executed from the role. I am having trouble assigning the content of the v_execute argument inside the statement: GRANT EXECUTE on…
Gonzalo Amador Demeneghi
  • 13
  • 5
1
vote
2 answers

Trigger PostgreSQL to update one value based on the sum of three others

I am trying to make a boolean become true when the sum of three integers is equal to 100 and false when the sum is not equal to 100. I am making a trigger function in Postgres to do that, but getting the following error: ERROR: …
Jonathan Prieto
  • 233
  • 4
  • 14
1
vote
0 answers

How to call a stored procedure inside another stored procedure in postgreSQL

I have a two tables, table_version_1 and table_version_2, I am trying to generate a new table based on these two tables. For which I have to write 4 stored procedures, procedure1, procedure2, procedure3, procedure4 I am triggering procedure1…
Sai sri
  • 515
  • 12
  • 25
1
vote
1 answer

Constructing array literal as input for PL/pgSQL function

How should an array with elements of a composite type be included in a function call? After consulting the resources online and attempting the different variants, I continue to get parser errors. Below I've included the types, tables, functions,…
Jorge Olivero
  • 3,705
  • 9
  • 27
  • 33
1
vote
0 answers

plpgsql function error: Hint: No function matches the given name and argument types. You might need to add explicit type casts

I am trying to make this function work. It's purpose is to loop through my query and extract merchants who transacted during different months, thus I need a sliding window. Ultimately, I would like to insert those in a different table but for now I…
Krissy
  • 11
  • 2
1
vote
1 answer

Postgres - SQL state: 22004 in EXECUTE function

I'm trying to make a function to count the number of rows in a column. The objective is pass the table and the column as a parameter in a function and return the number of rows. The function is: CREATE OR REPLACE FUNCTION…
Borja Burriel
  • 15
  • 4
1
vote
2 answers

Plpgsql seems to be deleting and inserting instead of updating - Why?

I am using postgreSQL 7.4. I have a large table , call it table_a: key1 INT NOT NULL, key2 INT NOT NULL, data INT NOT NULL, itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::timestamp without time zone))::INTEGER and a table…
youngthing
  • 384
  • 2
  • 13
1
vote
1 answer

How to concatenate strings to form a json object in Postgres?

I have a string coldata->>'f1' which I need to send dynamically to fetch JSON object. What I want exactly is: RAISE NOTICE 'OBJECT %', fc_data->'firstname'->>'value'; In my case I am getting the firstname dynamically in the variable coldata->>'f1'…
Sai sri
  • 515
  • 12
  • 25
1
vote
1 answer

Weird PostgreSQL null check behavior for custom types

I am trying to create a function which should map the table row to my custom type and return that as a result. If some column in that table is NULL, then IS NOT NULL check on my custom type does not work! Check the example: I have a simple custom…
elBastarde
  • 185
  • 3
  • 8
1
vote
1 answer

plpgsql function: execute query, process records and return original result set as table

I want a function that does the following: Executes a query and then processes all data records in the result set and then returns the original Result Set. So I have this table: employee_state_id | employee_id | start_date | end_date | state | …
jules
  • 357
  • 1
  • 3
  • 11
1
vote
1 answer

Substring should return Distinct values

I have a name column like this: Name ------ Roger RogerMan Roger That I need a query which would return distinct initials from the column. For example, in this case I want to return: Name ----- R RM RT I have tried a number of approaches for…
Garfield
  • 143
  • 11
1
vote
1 answer

Building a routine to generate CREATE TRIGGER code in Postgres

I haven't found a straightforward way to retrieve trigger definition code. I mean the trigger/binding declaration, not the trigger function. I figured I'd use some of the system catalogs to build up a script. The following, incomplete, version…
Morris de Oryx
  • 1,857
  • 10
  • 28
1
vote
1 answer

Conditionally perform SQL statements in a raw postgresql function?

I'm not exactly clear on whether or not it's possible to conditionally perform SQL statements using raw SQL functions in Postgres, or if you need to use plpgsql with IF statements for that. Basically, what I'm trying to do is this: CREATE OR REPLACE…
Ryan Peschel
  • 11,087
  • 19
  • 74
  • 136
1
vote
1 answer

PL/PGSQL Operator does not exist: information_schema.sql_identifier

I'm writing a function to write a dynamic query. This is the original query without function SELECT b.column_name, a.default_flag, CAST(AVG(a.payment_ratio) AS NUMERIC), CAST(MAX(a.payment_ratio) AS NUMERIC) FROM user_joined a,…
Iqbal Pratama
  • 139
  • 1
  • 5
  • 14
1
vote
1 answer

Game with quotes in PLPGSQL

I (still) ask for your help. I have a PLPGSQL script whose skeleton is good. I have 6 scripts on this basis that do the job properly. However, I have a slightly more complex 7th script. CREATE or replace FUNCTION maFonction(table_ori varchar,…
IFE
  • 21
  • 1
1 2 3
…
99
100