Tagged Questions
1
vote
1answer
53 views
Postgres : Executing SELECT within a transaction does not return most recent rows
Given a table layout that looks like this
inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...
inv_items_stock
===============
item_id bigint (inv_items)
qty ...
12
votes
1answer
7k 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 ...
2
votes
1answer
42 views
PL/pgSQL function or rCTE to detect depth of relations between two tables
I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
0
votes
0answers
35 views
Wrapper function with SECURITY DEFINER is slow [closed]
I have a strange behavior of wrapper function with SECURITY DEFINER option.
Function 1 - Queries data what is not directly accessible by user.
Function 2 - Is a wrapper that calls function 1 with no ...
2
votes
1answer
27 views
Prevent postgres function of running more than 1 instance at the same time
DBMS: Postgres 9.3.4
OS: Debian 7
I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
2
votes
1answer
202 views
How to pass a parameter into a function
At the moment I have a view created, see it here in the answer. How can I create a function on that cross tab, so I can pass a date, and get data for the specific date?
Also is it a good practice to ...
1
vote
3answers
43 views
How to log DML statements executed by a pl/pgsql function?
I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands.
When I set log_statement = 'mod', I can see nothing on the log ...
1
vote
1answer
316 views
Tree structure hierarchical query sorting
I have the following structure of table.
CREATE TABLE table1
(
serial_num integer NOT NULL,
parent_num integer,
parent_key text,
key_ary_num integer[],
level integer,
rank integer,
...
6
votes
2answers
278 views
Is this temp table behaviour documented?
Query Language (SQL) and PL/pgSQL functions treat temp tables differently:
begin;
create table foo(id) as values (1);
select * from foo;
/*
id
----
1
*/
savepoint s;
create function f() returns ...
0
votes
1answer
35 views
Postgresql Function Trying to Insert into 1 row instead of 2
Postgresql 9.3, Mac OS X 10.6.7
I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows.
First, here is the Table T1:
...
1
vote
2answers
75 views
Function execute query, manipulates result and then return the same result
I need a function that does something like this pseudocode:
function get_data() RETURN SET OF something... as
BEGIN
myResultSet = select id, some_other_column from ...... limit 20000;
update ...
3
votes
0answers
145 views
Is there a First() aggregate function in postgresql that returns the exact same type as the input?
Full question re-write
I'm looking a First() aggregate function.
Here I found something that almost works:
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement ...
3
votes
2answers
404 views
Call multiple functions from trigger?
I run PostgreSQL-9.2.4
Is it possible to call 2 functions from a trigger?
Let's say I have two functions for two different tables to be executed if following trigger fires:
Trigger:
CREATE TRIGGER ...
0
votes
1answer
33 views
Restricting Insert and Update to functions
We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving ...
2
votes
1answer
68 views
How to create function(table_name) that returns a set of the table
I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
1
vote
0answers
244 views
How to use the result of regexp_split_to_array() in stored procedure [closed]
I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table.
The point where I get stuck is the ...
1
vote
1answer
72 views
Function quote_nullable(timestamp without time zone) is not unique
When I run this query on "enterprisedb 9.2", it raises an error:
select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;
Error:
LINE 1: select quote_nullable(to_date('09-02-2014 ...
1
vote
3answers
99 views
How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?
I'm working on PostgreSQL and I have a table like this called words_table:
I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
2
votes
1answer
3k views
Postgres function assign query results to multiple variables
I need to assign values to 2 variable as below in Postgres function.
a := select col1 from tbl where ...
b := select col2 from tbl where ...
How can I assign 2 values to 2 variables in one line ...
0
votes
1answer
337 views
Calling another function inside a postgres function
I have a function A and function B.
Function A Name is f_a() and the code is:
Select id,address from A;
Function B Name is f_b() code is:
Select C.address,B.name
From
(Select id,name from B,
...
1
vote
3answers
261 views
Proper use of database functions for business logic
I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.
These ...
3
votes
1answer
98 views
PL/pgSQL functions and optimizations fences?
I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and ...
0
votes
1answer
172 views
use function arguments or variable in postgres function
I want to set the default value for the column building and floor in my table points in the below function called test(b,f)
Create or replace function test(b text, f text) returns void as
$$
Begin
...
0
votes
2answers
461 views
Recursive update for tree structure in PostgreSQL
I have the following structure of table.
id chNum parentid
--- ------ ---------
1 1 NULL
2 1.1 1
3 1.1.1 2
4 2 ...
0
votes
0answers
53 views
Postgresql function taking long time on newly restored database
I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute.
Now I have restored a new PostgreSQL 9.0 dump using following command:
pg_restore -hx.x.x.x -p5432 -d ...
2
votes
1answer
921 views
Postgresql function to create table
I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:
CREATE OR ...
1
vote
1answer
900 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
2
votes
1answer
232 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 ...
1
vote
2answers
1k 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
806 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 ...
5
votes
3answers
1k views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
-1
votes
1answer
201 views
Undefined reference to my function [closed]
I'm working on a project on postgresql 8.4 server side. I'm adding some functionality in order to store some query informations.
I need to call two functions (start_create_profile() and check()) ...
7
votes
3answers
1k views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
1
vote
1answer
455 views
PostgreSQL How do I convert struct text to plain *char in C functions?
I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected?
#include <stdio.h>
#include <string.h>
#include ...
3
votes
2answers
195 views
Custom Postgres function not working
I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get ...
2
votes
1answer
2k views
Transform all columns records to lowercase
I'm using PostgreSQL 9.1 and I have a users table with a login column.
login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. ...
4
votes
1answer
425 views
Is it possible to wrap aggregate functions in Postgres?
Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
4
votes
1answer
5k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
3
votes
1answer
2k views
Function/procedure to use dblink to fetch remote data and insert to multiple local tables
Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.
But now I was thinking of creating a ...
