Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.
plpgsql
4,379
questions
0
votes
1
answer
8
views
Error calling Redshift stored procedure with refcursor parameter
I am trying to fetch resultset in DataGrip console from a Redshift stored procedure. Here is my stored procedure body
CREATE OR REPLACE PROCEDURE test_schema.get_redshift_table_ddl(
p_schema_name ...
- 327
1
vote
1
answer
22
views
How to get the statement that triggered a trigger function when it is called as part of multi-statement query or function in PL/pgSQL?
I have an INSTEAD OF INSERT trigger on a view. Its trigger function calls current_query(). When inserting something directly into this view, everything works fine.
But when something is inserted into ...
- 1,822
1
vote
1
answer
33
views
How to Dynamically Create Tables and Functions in Supabase Using PL/pgSQL?
I am working on setting up a vector store in my Supabase database, and I need to execute a script for each new table I create. The script enables the pgvector extension, creates a table, and defines a ...
- 11
-2
votes
1
answer
54
views
Apache AGE Cypher query in dynamic PL/pgSQL functions
I'm using the official release from Apache AGE GitHub for PostgreSQL 14 installed via homebrew on a Mac. I've run into an issue related to creating functions with Cypher and PL/pgSQL.
I'm trying to ...
- 11
2
votes
1
answer
41
views
How do I pass JSON to a Postgres COPY tmp FROM PROGRAM command
I'm trying to use Postgres (PL/pgSQL) to communicate with a shell script that expects JSON data. A simplified example of what I'm trying to do is
CREATE OR REPLACE FUNCTION json_func(IN json_in JSONB, ...
- 1,203
0
votes
1
answer
43
views
How to use parametrized query with variable in PL/pgSQL?
DO
$$
DECLARE lang TEXT;
myvar dictionary_keys_view.it%TYPE;
begin
lang = 'it';
SELECT INTO myvar lang FROM dictionary_keys_view WHERE key = 'ENDED';
raise notice 'title id: %', myvar;...
0
votes
1
answer
35
views
Expression short cut evaluation in Postgresql
In C, the classic short cut evalution might be something like
if ( a != NULL && a->b == 0 )...
to avoid evaluating a structure reference if invalid...
I'm trying something similar in a ...
- 1
0
votes
0
answers
24
views
Supabase RPC: SQL Injection and other security issues
I am working on a PL/pgSQL function that will be exposed to the public via RPC (supabase allows this). The critical thing about this function is that it gets the column name as a string, which will ...
- 403
0
votes
2
answers
34
views
postgres- plsql function to get rows counts for specific columns with null data
i need to get rows counts for specific columns with null and insert the same rows count output into a table with result set of the query. For this requirement, i tried with below pl/sql code block in ...
-1
votes
0
answers
17
views
Returning values to Java Batch Insert from PostGres Function
When doing a batch call from a Prepared Statement in Java to a function in PostGres is it possible to set the returned number.
String query = "SELECT \"functionCall\"(?)
...
- 147
1
vote
1
answer
38
views
Analog for table-variable in postgres
I'm wrighting a stored function and I need to have table-variable like in MSSQL. I need to have possibility to insert/read/delete records from this table and use it in queries.
Temp tables are not ...
- 187
0
votes
1
answer
43
views
Error: "more than one row returned by a subquery used as an expression" in trigger function
CREATE OR REPLACE FUNCTION update_user_grade()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
IF EXISTS (
SELECT 1
FROM courses c
WHERE c.user_id = ...
0
votes
0
answers
52
views
How do I validate input from a defined type in PostGreSQL in a function?
I'm used to using a MERGE statement in MS SQL (SQL SERVER) to validate input in a Stored Procedure/Function that matches a defined table type, but I'm finding it difficult to replicate that behavior ...
- 39
0
votes
3
answers
48
views
SELECT INTO does not raise exception when no row is found
I have an empty table stock_holdings:
select qty,total_amount from stock_holdings where account_id=1 and ticker_cd='XYZ';
qty | total_amount
-----+--------------
(0 rows)
When I run the following ...
- 27
0
votes
1
answer
44
views
CRC16 CRC-CCITT (0xFFFF) function with PL/pgSQL
How to compute CRC16 CRC-CCITT (0xFFFF) function with PL/PGSQL , the same way as MySQL?
Code Function for PL/PGSQL
I try to convert from MYSQL to PL/PGSQL
DELIMITER //
CREATE DEFINER=`root`@`%` ...
