plpgsql Questions
5
According to the documentation (https://www.postgresql.org/docs/current/app-psql.html), even with AUTOCOMMIT set to off, PSQL issues an implicit BEGIN just before any command that is not already in...
Quartis asked 8/11, 2018 at 19:21
6
Solved
I have a text column that contains JSON and also plan text. I want to convert it to JSON, and then select a particular property. For example:
user_data
_________
{"user": {"name": "jim"}}
{"user":...
Inclinable asked 31/8, 2017 at 1:6
7
Solved
How can I declare an array like variable with two or three values and get them randomly during execution?
a := [1, 2, 5] -- sample sake
select random(a) -- returns random value
Any suggestion w...
Hagiographer asked 12/1, 2013 at 23:27
2
I want to use savepoint feature inside a function in PostgreSQL. I read that savepoint cannot be used inside functions in Postgres.
But while I rollback, I want to rollback to a specific point be...
Desperado asked 7/3, 2017 at 2:8
3
I want to get my current connection schema name I found that 'show search_path' returns the needed result but I need to use this result in the query.
How can I use "show search_path" in a...
Insectivorous asked 4/11, 2021 at 9:51
8
Solved
How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?
I have a function:
CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BOD...
Catacaustic asked 8/9, 2012 at 5:12
9
While executing below shown trigger code using ANT I am getting the error:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer"
Pos...
Eddy asked 17/8, 2010 at 5:6
3
Solved
I have this function in PostgreSQL, but I don't know how to return the result of the query:
CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
RETURNS SETOF RECORD AS
$$
BEGIN
SELECT tex...
Swisher asked 30/10, 2011 at 15:47
4
Solved
As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work?
create or replace function get_user_by_username(
username varchar(2...
Margravine asked 22/12, 2011 at 14:19
3
Solved
Getting into PL/pgSQL…
I'm still learning the syntax. Sometimes, I'd run into this error message:
ERROR: cannot use RETURN QUERY in a non-SETOF function
This sounds sort of cryptic and I coul...
Gibrian asked 15/4, 2019 at 11:42
3
Solved
I am a newbie in PostgreSQL (using v9.0) and wanted to know if using return next and Return query in the same function is possible without exiting the function before attaching the results in the r...
Deepfreeze asked 23/4, 2014 at 15:49
6
Is it possible to return multiple result sets from a PostgreSQL function, like in MSSQL:
CREATE FUNCTION test
AS
SELECT * FROM first_table
SELECT * FROM second_table
Outsail asked 16/4, 2009 at 15:31
4
Solved
I am trying to write a loop in a PL/pgSQL function in PostgreSQL 9.3 that returns a table. I used RETURN NEXT; with no parameters after each query in the loop, following examples I found, like:
pl...
Accomplishment asked 27/12, 2014 at 0:4
2
Solved
I have a plpgsql function in PostgreSQL 9.2 which returns a table. The function runs several SELECTs that return the same columns as the function and then either returns those results or raises an ...
Pneumonia asked 26/12, 2012 at 11:6
2
Solved
In plpgsql, I want to get the array contents one by one from a two dimension array.
DECLARE
m varchar[];
arr varchar[][] := array[['key1','val1'],['key2','val2']];
BEGIN
for m in select arr LOOP...
Beelzebub asked 20/3, 2012 at 8:21
2
Solved
I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this:
declare
a integer[] = array[1,2,3];
i bigint;
begin
for i in a loop
raise notice "% &q...
Horology asked 18/4, 2012 at 17:8
3
Solved
How to have the break statement in PostgreSQL? I have the structure like this:
for() {
for() {
if(somecondition)
break;
}
}
}
As per my understanding it should only break the inner for loop?
...
Darmstadt asked 2/3, 2013 at 10:2
5
Solved
I have a table in the database:
create table store (
...
n_status integer not null,
t_tag varchar(4)
t_name varchar,
t_description varchar,
dt_modified timestamp not null,
...
);
In my st...
Lepton asked 3/10, 2012 at 11:20
4
Solved
I have a function in plpgsql:
CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date)
RETURNS character varying AS $$
BEGIN
RETURN (SELECT date_in_bs
FROM core.date_conversion
WHERE da...
Welfarism asked 9/2, 2014 at 17:18
3
Solved
I'm using pgAdmin, and I want to have a simple raise notice; referring to this, I entered RAISE NOTICE 'note'; and got this error:
ERROR: syntax error at or near "RAISE"
LINE 1: RAISE NOT...
Salts asked 6/1, 2023 at 9:35
13
Solved
How do you view a stored function or procedure?
Say I have an old function without the original definition - I want to see what it is doing in pg/psql but I can't seem to figure out a way to do tha...
Unrivaled asked 19/8, 2010 at 18:11
7
Solved
My function is:
CREATE OR REPLACE FUNCTION FnUpdateSalegtab09
(
iacyrid Integer,iRepId Integer,iDrId Integer,ivrid Integer,imode smallint,itrno
varchar,itrdate timestamp,iacid Integer,ivrno var...
Bracknell asked 15/7, 2014 at 6:33
1
I'm trying to create query: searching stats of persons by phone number.
I start using INNER JOIN in internal query and now I don't understand how type I must return.
I'm trying to use view as type,...
Mailman asked 6/1, 2022 at 20:11
3
Solved
I've been looking at the documentation of postgresql triggers, but it seems to only show examples for row-level triggers, but I can't find an example for a statement-level trigger.
In particular,...
Fields asked 12/6, 2014 at 20:42
3
Solved
Maybe it's not new case, but I'm stack about it. This is the procedure that I use to run query, it run normally in MySQL, but not in PostgreSQL and I don't know how to do that. The procedure(in MyS...
Blackandwhite asked 29/3, 2018 at 3:54
1 Next >
© 2022 - 2025 — McMap. All rights reserved.