Tagged Questions
0
votes
1answer
37 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
10
votes
3answers
2k views
PostgreSQL Stored Procedure Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I ...
2
votes
1answer
36 views
How to turn off all RAISE NOTICE on production server?
I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
0
votes
1answer
76 views
How to use array variable in query in PostgreSQL
Create table t1 ( xcheck varchar[], name text );
CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
RETURNS record AS
DECLARE xrc as record;
execute 'select name from t1 where xcheck @> ...
0
votes
1answer
85 views
Error: set_valued function called in context that cannot accept a set. What is it about?
I use Postgresql 9.1, with ubuntu 12.04.
Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
1
vote
1answer
60 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
2
votes
1answer
51 views
How to access the number of rows accumulated by RETURNs in PL/pgSQL
When performing
RETURN QUERY ...
in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
0
votes
1answer
25 views
Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?
In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth.
For example:
create or replace function ...
1
vote
2answers
83 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
0
votes
2answers
167 views
Interactive INSERT / UPDATE function to implement UPSERT
I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
2
votes
2answers
66 views
String handling in postgres 8.4
This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).
I thought I ...
0
votes
1answer
64 views
Difference between return next and return record
What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
1
vote
1answer
275 views
Declare variable of table type in PL/pgSQL
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:
q1 = select * from foo;
q2 = select * from bar;
...
2
votes
1answer
163 views
returning set of records in stored procedure
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:
create or ...
4
votes
1answer
683 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
3
votes
1answer
92 views
PostgreSQL 8.4: How to tell if a procedural language is installed or not?
I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
5
votes
3answers
195 views
Casting issue when calling a function with composite type parameter
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.
what could be the issue?
CREATE OR REPLACE FUNCTION ...
0
votes
1answer
308 views
Save output from multiple SQL SELECT commands to a file
Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a ...
3
votes
1answer
88 views
PostgreSQL Return Joined Results of Select
I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one:
CREATE OR REPLACE FUNCTION api.book_list()
RETURNS TABLE (
id BIGINT,
published ...
3
votes
1answer
214 views
Distinguish between schema and database
I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code:
DECLARE
v_tmp_host scan.location.alias%TYPE;
v_ip_address character varying;
BEGIN
...
3
votes
1answer
125 views
Check what event called the trigger on PostgreSQL?
I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.
Oracle triggers can check this in a simple 'IF' statement:
IF INSERTING, IF UPDATING or even IF DELETING
Is there a ...
2
votes
1answer
146 views
Getting a random row from PostgreSQL?
I want to get a random row from my table by id.
My table:
ID|Word |Dificult|Category_id|
1 |'Dumb' |'Easy' | 3 |
2 |'Leopard'|'Medium'| 6 |
If my user selects a category and ...
2
votes
2answers
3k views
Postgres plpgsql - Using a variable inside of a dynamic create statement
Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:
...
DECLARE
tblVar varchar := "myTable";
BEGIN
EXECUTE 'CREATE TABLE $1 (
foo ...
1
vote
2answers
320 views
Running a CTE query in a loop using PL/pgSQL
I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
0
votes
0answers
29 views
Running a CTE query in a loop using PL/PGSQL [duplicate]
Possible Duplicate:
Running a CTE query in a loop using PL/pgSQL
(Cross-post from http://stackoverflow.com/q/12625914/1555778)
I'm trying to execute query that is repeatedly called in a ...
5
votes
2answers
364 views
PostgreSQL procedural languages - differences between PL/pgSQL and SQL
Can anybody please summarize the differences between:
http://www.postgresql.org/docs/9.1/static/xfunc-sql.html
and
http://www.postgresql.org/docs/9.1/static/plpgsql.html
?
Main points:
...
2
votes
2answers
212 views
Bug in PL/pgSQL function creation
I don't know if this question better suits here or in SO ...
This is a script that I'd like to launch (the code of the function was copied from a question on SO):
\c mydb
create or replace function ...
8
votes
2answers
161 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
0
votes
1answer
216 views
PL/pgSQL function + client-side lo_import
I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this:
create function add_file(flag integer, sth varchar) returns void as
begin
...
2
votes
2answers
678 views
Dynamic access to record column in plpgsql function
How can I address a column from a record in a plpgsql function dynamically?
In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
