plpgsql

PostgreSQL custom exceptions?

In Firebird we can declare custom exceptions like so: CREATE EXCEPTION EXP_CUSTOM_0 'Exception: Custom exception'; these are stored at the database level. In stored procedures, we can raise the exception like so: EXCEPTION EXP_CUSTOM_0 ; Is there an equivalent in PostgreSQL ? ...

Query has no destination for result data after Trigger

Hello, i have a problem with me trigger. Every time i insert a new line i will check if the article not sold. I can do it in the software but i think its better when the DB this does. -- Create function CREATE OR REPLACE FUNCTION checkSold() RETURNS TRIGGER AS $checkSold$ BEGIN SELECT offer_id FROM offer WHERE offer_id = NE...

Regular expression replace in PL/pgSQL

If I have the following input (excluding quotes): "The ancestral territorial imperatives of the trumpeter swan" How can I collapse all multiple spaces to a single space so that the input is transformed to: "The ancestral territorial imperatives of the trumpeter swan" This is going to be used in a trigger function on insert/...

Problem with Postgres FOR LOOP

Hi all, Ich have a problem in postgres function: CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS $$ DECLARE catNumber int DEFAULT 0; DECLARE cat RECORD; BEGIN WITH RECURSIVE children(categoryid,category_fk) AS ( SELECT categoryid, category_fk ...

Can a PL/pgSQL function contain a dynamic subquery?

I am writing a PL/pgSQL function. The function has input parameters which specify (indirectly), which tables to read filtering information from. The function embeds business logic which allows it to select data from different tables based on the input arguments. The function dynamically builds a subquery which returns filtering data whi...

How to write this function as a pL/pgSQl function ?

I am trying to implement some business logic in a PL/pgSQL function. I have hacked together some pseudo code that explains the type of business logic I want to include in the function. Note: This function returns a table, so I can use it in a query like: SELECT A.col1, B.col1 FROM (SELECT * from some_table_returning_func(1, 1, 2, 3) a...

PostgreSQL storing paths for reference in scripts

I'm trying to find the appropriate place to store a system path in PostgreSQL. What I'm trying to do is load values into a table using the COPY command. However, since I will be referring to the same file path regularly I want to store that path in one place. I've tried creating a function to return the appropriate path, but I get a syn...

trigger didn't fired by using copy from command

I have populate a table using the copy from command which in turn will create record in summary table. While after the copy command successfully run, I can not see any record in the summary table. Anyone can shed some light on me? Pls find the table as well as the store procedure below:- CREATE TABLE apache_log ( log_name chara...

How to find the first and last occurrences of a specific character inside a string in PostgreSQL

I want to find the first and the last occurrences of a specific character inside a string. As an example, consider a string named "2010-####-3434", and suppose the character to be searched for is "#". The first occurrence of hash inside the string is at 6-th position, and the last occurrence is at 9-th position. ...

text search query biulding in postgres

Hi , I need to bulid a stored procedure that takes input an array of varchars . It will search for these using syntax like SELECT * FROM mytable WHERE search_index @@ to_tsquery(' '); If i give input to the procedure like tom ,dick,harry the query should be dynamically build like SELECT * FROM mytable WHERE search_index @@ to_tsque...

How to call Postgres function returning SETOF record?

I have written the following function: -- Gets stats for all markets CREATE OR REPLACE FUNCTION GetMarketStats ( ) RETURNS SETOF record AS $$ BEGIN SELECT 'R approved offer' AS Metric, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketAPlus24, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 36 T...

postgresql: Is it possible to dynamically loop through a table's column

Hi, I have a trigger function for a table test which has the following code snippet - IF TG_OP='UPDATE' THEN IF OLD.locked > 0 AND ( OLD.org_id <> NEW.org_id OR OLD.document_code <> NEW.document_code OR -- Other columns .......................... ) THEN RAISE EXCEPTION 'Message'; /* R...

Remove redundant function call for column and column's count

Problem In the following query, plr_stations is called twice: once to limit the WHERE clause; and once to count the number of results it returned. The code resembles: SELECT m.*, s.*, ( SELECT count(1) FROM climate.plr_stations('48.5146','-123.4447') ) AS count_stations FROM climate.station s, climate.measure...

Variable containing the number of rows affected by previous DELETE? (in a function)

I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the concept. DECLARE re1 feeds_item.shareurl%TYPE; BEGIN SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+\.html)$',...

Determining the column name passed to a Pg function

I have a PL/pgsql function like so CREATE OR REPLACE FUNCTION foo(colname TEXT, col INT) RETURNS REAL AS $$ BEGIN IF (colname = 'a') THEN RETURN (col * 1.5); ELSIF (colname = 'b') THEN RETURN (col * 2.5); ELSIF (colname = 'c') THEN RETURN (col * 3.5); .. and so...

pgSQL date field Conparison?

Hi all, I have a pgsql database , i want to compare two fields with there timestamp values. basic query select t1.valu1, t1.value2 from table1 as t1 where t1.valu1 == t1.valu2 With TimeStamp comparison select t1.valu1, t1.value2 from table1 as t1 where EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE t1.valu1 ) == EXTRACT(EPOCH FROM...

how create function with argumet as subselect?

Hello I like create function for select and changed me data CREATE OR REPLACE FUNCTION PublicatedTask( argument ) RETURNS SETOF task AS $$DECLARE f task%ROWTYPE; BEGIN FOR f IN SELECT * FROM Task where layer IN $1 and publicationin<>0 ORDER BY id LOOP if (f.publicationIN = 1) then f.description=''; end if; ...

Psycopg2 callproc and sql parameters

I got some SQL function CREATE OR REPLACE FUNCTION tools.update_company(IN company_id integer, OUT value integer) RETURNS integer AS $BODY$ BEGIN select * into value from function_making_int(company_id) END;$BODY$ and from Psycopg2 (its inside Django if that matters) I do c = connection.cursor() c.callproc('tools.update_compa...

postgresql: using NEW.* in dynamic command for EXECUTE

hi i try to create a plpgsql trigger for postgresql 8.3 which automatically partitions a table on before insert by the id column if the destination table doesnt exist it will be created, and the insert goes there so i created the insert statement with the new table name like this exec_insert := 'INSERT INTO '||TG_TABLE_SCHEMA||'.'||T...

PostgreSQL and pl/pgsql SYNTAX to update fields based on SELECT and FUNCTION (while loop, DISTINCT COUNT)

I have a large database, that I want to do some logic to update new fields. The primary key is "id" for the table harvard_assignees The LOGIC GOES LIKE THIS Select all of the records based on "id" For each record (WHILE), if (state is NOT NULL && country is NULL), update country_out = "US" ELSE update country_out=country I see step...