Search Results

Search found 47 results on 2 pages for 'plpgsql'.

Page 2/2 | < Previous Page | 1 2 

• not returning anything from postgresql function?

  - by netllama

  Is it possible for a PostgreSQL plpgsql function to not return anything? I've created a function, and I don't need it to return anything at all, as it performs a complex SQL query, and inserts the results of that query into another table (SELECT INTO ....). Thus, I have no need or interest in having the function return any output or value. Unfortunately, when I try to omit the RETURN clause of the function declaration, I can't create the function. Is it possible for a PostgreSQL plpgsql function to not return anything?

  Read the article

• What are the rules governing how a bind variable can be used in Postgres and where is this defined?

  - by Craig Miles

  I can have a table and function defined as: CREATE TABLE mytable ( mycol integer ); INSERT INTO mytable VALUES (1); CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol = l_myvar; RETURN l_myrow; END; $$ LANGUAGE plpgsql; In this case l_myvar acts as a bind variable for the value passed when I call: SELECT * FROM myfunction(1); and returns the row where mycol = 1 If I redefine the function as: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (l_myvar); RETURN l_myrow; END; $$ LANGUAGE plpgsql; SELECT * FROM myfunction(1); still returns the row where mycol = 1 However, if I now change the function definition to allow me to pass an integer array and try to this array in the IN clause, I get an error: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer[]) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (array_to_string(l_myvar, ',')); RETURN l_myrow; END; $$ LANGUAGE plpgsql; Analysis reveals that although: SELECT array_to_string(ARRAY[1, 2], ','); returns 1,2 as expected SELECT * FROM myfunction(ARRAY[1, 2]); returns the error operator does not exist: integer = text at the line: WHERE mycol IN (array_to_string(l_myvar, ',')); If I execute: SELECT * FROM mytable WHERE mycol IN (1,2); I get the expected result. Given that array_to_string(l_myvar, ',') evaluates to 1,2 as shown, why arent these statements equivalent. From the error message it is something to do with datatypes, but doesnt the IN(variable) construct appear to be behaving differently from the = variable construct? What are the rules here? I know that I could build a statement to EXECUTE, treating everything as a string, to achieve what I want to do, so I am not looking for that as a solution. I do want to understand though what is going on in this example. Is there a modification to this approach to make it work, the particular example being to pass in an array of values to build a dynamic IN clause without resorting to EXECUTE? Thanks in advance Craig

  Read the article

• Learning PostgreSql: polymorphism

  - by Alexander Kuznetsov

  Functions in PL/PgSql are polymorphic, which is very different from T-SQL. Demonstrating polymorphism For example, the second CREATE FUNCTION in the following script does not replace the first function - it creates a second one: CREATE OR REPLACE FUNCTION public .GetQuoteOfTheDay ( someNumber INTEGER ) RETURNS VARCHAR AS $body$ BEGIN RETURN 'Say my name.' ; END ; $body$ LANGUAGE plpgsql ; CREATE OR REPLACE FUNCTION public .GetQuoteOfTheDay ( someNumber REAL ) RETURNS VARCHAR AS $body$ BEGIN RETURN...(read more)

  Read the article

• Porting Oracle Procedure to PostgreSQL

  - by Grasper

  I am porting an Oracle function into Postgres PGPLSQL.. I have been using this guide: http://www.postgresql.org/docs/8.1/static/plpgsql.html CREATE OR REPLACE PROCEDURE DATA_UPDATE (mission NUMBER, task NUMBER) AS BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT NVL(SUM(RR.TRQ_FUEL_OFFLOAD),0), NVL(SUM(RR.TRQ_NUMBER_RECEIVERS),0) FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID AND MO.MO_INT_ID = MRP.MO_INT_ID AND MRP.REQ_INT_ID = RR.REQ_INT_ID) WHERE MO.MSN_INT_ID = mission AND MO.MO_INT_ID = task ; END IF ; COMMIT ; END ; I've got it this far: CREATE OR REPLACE FUNCTION DATA_UPDATE (NUMERIC, NUMERIC) RETURNS integer as ' DECLARE mission ALIAS for $1; task ALIAS for $2; BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT COALESCE(SUM(RR.TRQ_FUEL_OFFLOAD),0), COALESCE(SUM(RR.TRQ_NUMBER_RECEIVERS),0) FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID AND MO.MO_INT_ID = MRP.MO_INT_ID AND MRP.REQ_INT_ID = RR.REQ_INT_ID) WHERE MO.MSN_INT_ID = mission AND MO.MO_INT_ID = task ; END IF; COMMIT; END; ' LANGUAGE plpgsql; This is the error I get: ERROR: syntax error at or near "SELECT" LINE 1: ...OTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT COA... I do not know why this isn't working... any ideas?

  Read the article

• PL/PGSQL function, having trouble accessing a returned result set from psycopg2...

  - by Paul

  I have this pl/pgsql function: CREATE OR REPLACE FUNCTION get_result(id integer) RETURNS SETOF my_table AS $ DECLARE result_set my_table%ROWTYPE; BEGIN IF id=0 THEN SELECT INTO result_set my_table_id, my_table_value FROM my_table; ELSE SELECT INTO result_set my_table_id, my_table_value FROM my_table WHERE my_table_id=id; END IF; RETURN; END; $ LANGUAGE plpgsql; I am trying to use this with Python's psycopg2 library. Here is the python code: import psycopg2 as pg conn = pg.connect(host='myhost', database='mydatabase', user='user', password='passwd') cur = conn.cursor() return cur.execute("SELECT * FROM get_result(0);") # returns NoneType However, if i just do the regular query, I get the correct set of rows back: ... return cur.execute("SELECT my_table_id, my_table_value FROM mytable;") # returns iterable result set Theres obviously something wrong with my pl/pgsql function, but I can't seem to get it right. I also tried using RETURN result_set; instead of just RETURN in the 10th line of my plpgsql function, but got an error from postgres.

  Read the article

• Sending one record from cursor to another function Postgres

  - by PylonsN00b

  FYI: I am completely new to using cursors... So I have one function that is a cursor: CREATE FUNCTION get_all_product_promos(refcursor, cursor_object_id integer) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT * FROM promos prom1 JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id) WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date AND promo_objects.object_id = cursor_object_id UNION SELECT prom2.promo_id FROM promos prom2 JOIN promo_buy_objects ON (prom2.promo_id = promo_buy_objects.promo_id) LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0) AND prom2.active = true AND now() BETWEEN prom2.start_date AND prom2.end_date AND promo_buy_objects.object_id = cursor_object_id; RETURN $1; END; ' LANGUAGE plpgsql; SO then in another function I call it and need to process it: ... --Get the promotions from the cursor SELECT get_all_product_promos('promo_cursor', this_object_id) updated := FALSE; IF FOUND THEN --Then loop through your results LOOP FETCH promo_cursor into this_promotion --Preform comparison logic -this is necessary as this logic is used in other contexts from other functions SELECT * INTO best_promo_results FROM get_best_product_promos(this_promotion, this_object_id, get_free_promotion, get_free_promotion_value, current_promotion_value, current_promotion); ... SO the idea here is to select from the cursor, loop using fetch (next is assumed correct?) and put the record fetched into this_promotion. Then send the record in this_promotion to another function. I can't figure out what to declare the type of this_promotion in get_best_product_promos. Here is what I have: CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion record, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer) RETURNS... It tells me: ERROR: plpgsql functions cannot take type record OK first I tried: CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion get_all_product_promos, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer) RETURNS... Because I saw some syntax in the Postgres docs showed a function being created w/ a input parameter that had a type 'tablename' this works, but it has to be a tablename not a function :( I know I am so close, I was told to use cursors to pass records around. So I studied up. Please help.

  Read the article

• How to write my own global lock / unlock functions for PostgreSQL

  - by rafalmag

  I have postgresql (in perlu) function getTravelTime(integer, timestamp), which tries to select data for specified ID and timestamp. If there are no data or if data is old, it downloads them from external server (downloading time ~300ms). Multiple process use this database and this function. There is an error when two process do not find data and download them and try to do an insert to travel_time table (id and timestamp pair have to be unique). I thought about locks. Locking whole table would block all processes and allow only one to proceed. I need to lock only on id and timestamp. pg_advisory_lock seems to lock only in "current session". But my processes uses their own sessions. I tried to write my own lock/unlock functions. Am I doing it right? I use active waiting, how can I omit this? Maybe there is a way to use pg_advisory_lock() as global lock? My code: CREATE TABLE travel_time_locks ( id_key integer NOT NULL, time_key timestamp without time zone NOT NULL, UNIQUE (id_key, time_key) ); ------------ -- Function: mylock(integer, timestamp) DROP FUNCTION IF EXISTS mylock(integer, timestamp) CASCADE; -- Usage: SELECT mylock(1, '2010-03-28T19:45'); -- function tries to do a global lock similar to pg_advisory_lock(key, key) CREATE OR REPLACE FUNCTION mylock(id_input integer, time_input timestamp) RETURNS void AS $BODY$ DECLARE rows int; BEGIN LOOP BEGIN -- active waiting here !!!! :( INSERT INTO travel_time_locks (id_key, time_key) VALUES (id_input, time_input); EXCEPTION WHEN unique_violation THEN CONTINUE; END; EXIT; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 1; ------------ -- Function: myunlock(integer, timestamp) DROP FUNCTION IF EXISTS myunlock(integer, timestamp) CASCADE; -- Usage: SELECT myunlock(1, '2010-03-28T19:45'); -- function tries to do a global unlock similar to pg_advisory_unlock(key, key) CREATE OR REPLACE FUNCTION myunlock(id_input integer, time_input timestamp) RETURNS integer AS $BODY$ DECLARE BEGIN DELETE FROM ONLY travel_time_locks WHERE id_key=id_input AND time_key=time_input; RETURN 1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 1;

  Read the article

• PostgreSQL triggers and passing parameters

  - by iandouglas

  This is a multi-part question. I have a table similar to this: CREATE TABLE sales_data ( Company character(50), Contract character(50), top_revenue_sum integer, top_revenue_sales integer, last_sale timestamp) ; I'd like to create a trigger for new inserts into this table, something like this: CREATE OR REPLACE FUNCTION add_contract() RETURNS VOID DECLARE myCompany character(50), myContract character(50), BEGIN myCompany = TG_ARGV[0]; myContract = TG_ARGV[1]; IF (TG_OP = 'INSERT') THEN EXECUTE 'CREATE TABLE salesdata_' || $myCompany || '_' || $myContract || ' ( sale_amount integer, updated TIMESTAMP not null, some_data varchar(32), country varchar(2) ) ;' EXECUTE 'CREATE TRIGGER update_sales_data BEFORE INSERT OR DELETE ON salesdata_' || $myCompany || '_' || $myContract || ' FOR EACH ROW EXECUTE update_sales_data( ' || $myCompany || ',' || $myContract || ', revenue);' ; END IF; END; $add_contract$ LANGUAGE plpgsql; CREATE TRIGGER add_contract AFTER INSERT ON sales_data FOR EACH ROW EXECUTE add_contract() ; Basically, every time I insert a new row into sales_data, I want to generate a new table where the name of the table will be defined as something like "salesdata_Company_Contract" So my first question is how can I pass the Company and Contract data to the trigger so it can be passed to the add_contract() stored procedure? From my stored procedure, you'll see that I also want to update the original sales_data table whenever new data is inserted into the salesdata_Company_Contract table. This trigger will do something like this: CREATE OR REPLACE FUNCTION update_sales_data() RETURNS trigger as $update_sales_data$ DECLARE myCompany character(50) NOT NULL, myContract character(50) NOT NULL, myRevenue integer NOT NULL BEGIN myCompany = TG_ARGV[0] ; myContract = TG_ARGV[1] ; myRevenue = TG_ARGV[2] ; IF (TG_OP = 'INSERT') THEN UPDATE sales_data SET top_revenue_sales = top_revenue_sales + 1, top_revenue_sum = top_revenue_sum + $myRevenue, updated = now() WHERE Company = $myCompany AND Contract = $myContract ; ELSIF (TG_OP = 'DELETE') THEN UPDATE sales_data SET top_revenue_sales = top_revenue_sales - 1, top_revenue_sum = top_revenue_sum - $myRevenue, updated = now() WHERE Company = $myCompany AND Contract = $myContract ; END IF; END; $update_sales_data$ LANGUAGE plpgsql; This will, of course, require that I pass several parameters around within these stored procedures and triggers, and I'm not sure (a) if this is even possible, or (b) practical, or (c) best practice and we should just put this logic into our other software instead of asking the database to do this work for us. To keep our table sizes down, as we'll have hundreds of thousands of transactions per day, we've decided to partition our data using the Company and Contract strings as part of the table names themselves so they're all very small in size; file IO for us is faster and we felt we'd get better performance. Thanks for any thoughts or direction. My thinking, now that I've written all of this out, is that maybe we need to write stored procedures where we pass our insert data as parameters, and call that from our other software, and have the stored procedure do the insert into "sales_data" then create the other table. Then, have a second stored procedure to insert new data into the salesdata_Company_Contract tables, where the table name is passed to the stored proc as a parameter, and again have that stored proc do the insert, then update the main sales_data table afterward. What approach would you take?

  Read the article

• Dynamic upsert in postgresql

  - by Daniel

  I have this upsert function that allows me to modify the fill_rate column of a row. CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS ' DECLARE num ALIAS FOR $1; dat ALIAS FOR $2; BEGIN LOOP -- First try to update. UPDATE alarming SET fill_rate = dat WHERE equipid = num; IF FOUND THEN RETURN; END IF; -- Since its not there we try to insert the key -- Notice if we had a concurent key insertion we would error BEGIN INSERT INTO alarming (equipid, fill_rate) VALUES (num, dat); RETURN; EXCEPTION WHEN unique_violation THEN -- Loop and try the update again END; END LOOP; END; ' LANGUAGE 'plpgsql'; Is it possible to modify this function to take a column argument as well? Extra bonus points if there is a way to modify the function to take a column and a table.

  Read the article

• How to apply a update after an inser or update POSTGRESQL Trigger

  - by user3718906

  How to apply an update after an insert or update in POSTGRESQL; I have got a table which has a field lastupdate; I want that field to be set up whenever the row is updated or when it was inserted. I tried this trigger, but It is not working! HELP!! CREATE OR REPLACE FUNCTION fn_update_profile() RETURNS TRIGGER AS $update_profile$ BEGIN IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN UPDATE profile SET lastupdate=now() where oid=OLD.oid; RETURN NULL; ELSEIF (TG_OP = 'DELETE') THEN RETURN NULL; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $update_profile$ LANGUAGE plpgsql;

  Read the article

• How can I use "IF statements" in a postgres trigger

  - by Dan B

  I have a trigger function that I only want to fire on certain instances of INSERTS, in this case, if do_backup = true. If it fires in all instances, I get an infinite loop. The logic seems pretty simple to me, and the rest of the function works. But the trigger function does not seem to register my conditional and always runs, even when backup = true. CREATE OR REPLACE FUNCTION table_styles_backup() RETURNS TRIGGER AS $table_styles_backup$ DECLARE ... do_backup boolean; BEGIN SELECT backup INTO do_backup FROM table_details WHERE id=NEW.table_meta_id; IF (do_backup = true) THEN ... INSERT INTO table_styles_versions ( ... ) VALUES ( ... ); END IF; RETURN NULL; END; $table_styles_backup$ LANGUAGE plpgsql; CREATE TRIGGER table_styles_backup AFTER INSERT ON table_styles FOR EACH ROW EXECUTE PROCEDURE table_styles_backup();

  Read the article

• Return multiple results using dynamic sql (postgresql 8.2)

  - by precose

  I want to loop through schemas and get a result set that looks like this: Count 5 834 345 34 984 However, I can't get it to return anything using dynamic sql...I've tried everything but 8.2 is being a real pain. Here is my function: CREATE OR REPLACE FUNCTION dwh.adam_test4() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE myschema text; rec RECORD; BEGIN FOR myschema IN select distinct c.table_schema, d.p_id from information_schema.tables t inner join information_schema.columns c on (t.table_schema = t.table_schema and t.table_name = c.table_name) join dwh.sgmt_clients d on c.table_schema = lower(d.userid) where c.table_name = 'fact_members' and c.column_name = 'debit_card' and t.table_schema NOT LIKE 'pg_%' and t.table_schema NOT IN ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd') order by table_schema LOOP EXECUTE 'select count(ucic) from '|| myschema || '.' ||'fact_members where debit_card = ''yes''' into rec; RETURN rec; END LOOP; END $function$

  Read the article

• Determining child count of path

  - by sqlnewbie

  I have a table whose 'path' column has values and I would like to update the table's 'child_count' column so that I get the following output. path | child_count --------+------------- | 5 /a | 3 /a/a | 0 /a/b | 1 /a/b/c | 0 /b | 0 My present solution - which is way too inefficient - uses a stored procedure as follows: CREATE FUNCTION child_count() RETURNS VOID AS $$ DECLARE parent VARCHAR; BEGIN FOR parent IN SELECT path FROM my_table LOOP DECLARE tokens VARCHAR[] := REGEXP_SPLIT_TO_ARRAY(parent, '/'); str VARCHAR := ''; BEGIN FOR i IN 2..ARRAY_LENGTH(tokens, 1) LOOP UPDATE my_table SET child_count = child_count + 1 WHERE path = str; str := str || '/' || tokens[i]; END LOOP; END; END LOOP; END; $$ LANGUAGE plpgsql; Anyone knows of a single UPDATE statement that does the same thing?

  Read the article

• consistency of Trigger Procedure (before row trigger) Postgresql

  - by elgcom

  Using Postgresql. I try to use TRIGGER procedure to make some consistency check on INSERT. The question is ...... whether "BEFORE INSERT FOR EACH ROW" can make sure each row to insert "checked" and "inserted" one after another? do I need extra lock on table to survive from concurrent insert? check for new row1 - insert row1 - check for new row2 - insert row2 -- -- -- unexpired product name is unique. CREATE TABLE product ( "name" VARCHAR(100) NOT NULL, "expired" BOOLEAN NOT NULL ); CREATE OR REPLACE FUNCTION check_consistency() RETURNS TRIGGER AS $$ BEGIN IF EXISTS (SELECT * FROM product WHERE name=NEW.name AND expired='false') THEN RAISE EXCEPTION 'duplicated!!!'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_check_consistency BEFORE INSERT ON product FOR EACH ROW EXECUTE PROCEDURE check_consistency(); -- INSERT INTO product VALUES("prod1", true); INSERT INTO product VALUES("prod1", false); INSERT INTO product VALUES("prod1", false); // exception! this is OK name | expired ============== p1 | true p1 | true p1 | false This is not OK name | expired ============== p1 | true p1 | false p1 | false or maybe I should ask, how can I use Trigger to implement "Primary" or "Unique" constraint-like SQL.

  Read the article

• Troubleshooting PG Function

  - by Grasper

  I have this function: CREATE OR REPLACE FUNCTION CREATE_AIRSPACE_AVAILABILITY_RECORD (cur_user VARCHAR, start_time VARCHAR, start_date VARCHAR, end_time VARCHAR, end_date VARCHAR, airspace_name VARCHAR) RETURNS VOID AS ' DECLARE c_user ALIAS for $1; BEGIN IF start_time IS NULL OR start_date IS NULL OR end_time IS NULL OR end_date IS NULL THEN INSERT INTO c_user.AIRSPACE_AVAILABILITY (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT) SELECT airspace_name, 1, ABP.ABP_START_DT, ABP.ABP_STOP_DT FROM ABP WHERE EXISTS (SELECT ASP.ASP_AIRSPACE_NM FROM AIRSPACE ASP WHERE ASP.ASP_AIRSPACE_NM = airspace_name); ELSIF start_time IS NOT NULL AND start_date IS NOT NULL AND end_time IS NOT NULL AND end_date IS NOT NULL THEN INSERT INTO c_user.AIRSPACE_AVAILABILITY (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT) SELECT airspace_name, 1, TO_DATE(start_date||start_time,''YYMMDDHH24MI''), TO_DATE(end_date||end_time,''YYMMDDHH24MI'') FROM DUAL WHERE EXISTS (SELECT ASP.ASP_AIRSPACE_NM FROM c_user.AIRSPACE ASP WHERE ASP.ASP_AIRSPACE_NM = airspace_name); END IF; END ; ' LANGUAGE plpgsql; I try calling it like so: select * from CREATE_AIRSPACE_AVAILABILITY_RECORD('user1','','','','',''); and I get this error: ERROR: schema "c_user" does not exist SQL state: 3F000 Context: SQL statement "INSERT INTO c_user.AIRSPACE_AVAILABILITY (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT) SELECT $1 , 1, TO_DATE( $2 || $3 ,'YYMMDDHH24MI'), TO_DATE( $4 || $5 ,'YYMMDDHH24MI') FROM DUAL WHERE EXISTS (SELECT ASP.ASP_AIRSPACE_NM FROM c_user.AIRSPACE ASP WHERE ASP.ASP_AIRSPACE_NM = $1 )" PL/pgSQL function "create_airspace_availability_record" line 23 at SQL statement Why isn't c_user being replaced with my param (user1)?

  Read the article

• postgresql error - ERROR: input is out of range

  - by CaffeineIV

  The function below keeps returning this error message. I thought that maybe the double_precision field type was what was causing this, and I tried to use CAST, but either that's not it, or I didn't do it right... Help? Here's the error: ERROR: input is out of range CONTEXT: PL/pgSQL function "calculate_distance" line 7 at RETURN ********** Error ********** ERROR: input is out of range SQL state: 22003 Context: PL/pgSQL function "calculate_distance" line 7 at RETURN And here's the function: CREATE OR REPLACE FUNCTION calculate_distance(character varying, double precision, double precision, double precision, double precision) RETURNS double precision AS $BODY$ DECLARE earth_radius double precision; BEGIN earth_radius := 3959.0; RETURN earth_radius * acos(sin($2 / 57.2958) * sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) * cos(($5 / 57.2958) - ($3 / 57.2958))); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION calculate_distance(character varying, double precision, double precision, double precision, double precision) OWNER TO postgres; //I tried changing (unsuccessfully) that RETURN line to: RETURN CAST( (earth_radius * acos(sin($2 / 57.2958) * sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) * cos(($5 / 57.2958) - ($3 / 57.2958))) ) AS text);

  Read the article

• Error in Postgres execute

  - by RAJA

  I'm using this function... -- Function: dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) -- DROP FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying); CREATE OR REPLACE FUNCTION dbo.sp_acc_createaccount(IN in_orgmgrtype character varying, INOUT in_parentid integer, IN in_levelid integer, IN in_name character varying, IN in_phone character varying, IN in_webpage character varying, IN in_owner character varying, OUT out_accountid integer) RETURNS record AS $BODY$ DECLARE l_CoID int; l_CurrID int; l_OrgMgrId int; errmsg varchar(250); BEGIN IF in_ParentID = -1 THEN errmsg := 'execute sp_Acc_GetCompanyIDForUser failed'; l_CoID := dbo.sp_Acc_GetCompanyIDForUser(in_user); IF l_CoID = -2 THEN RAISE EXCEPTION 'execute sp_Acc_GetCompanyIDForUser failed'; END IF; errmsg := 'execute sp_Acc_GetOrgMgrIDForCompany failed'; l_OrgMgrID := dbo.sp_Acc_GetOrgMgrIDForCompany(in_OrgMgrType, l_CoID); IF l_OrgMgrID = -2 THEN RAISE EXCEPTION 'execute sp_Acc_GetOrgMgrIDForCompany failed'; END IF; in_ParentID := l_OrgMgrID; ELSE errmsg := 'Select orgmgrid failed'; SELECT OrgMgrID INTO l_CurrID FROM dbo.OrgMgr WHERE Name = in_Name AND ParentID = in_ParentID; END IF; -- if not, add it IF l_CurrID IS NULL THEN errmsg := 'Insert into orgmgr(account creation) failed'; INSERT INTO dbo.OrgMgr (ParentID, LevelID, Name, PrimaryPhone, WebPage, Owner) VALUES (in_ParentID, in_LevelID, in_Name, in_Phone, in_WebPage, in_Owner); out_AccountID := currval('dbo.OrgMgr_accountid_seq'); ELSE out_AccountID := -1; END IF; COMMIT; EXCEPTION WHEN RAISE_EXCEPTION THEN out_AccountID := 99; RAISE NOTICE 'ERROR : %',errmsg; WHEN OTHERS THEN out_AccountID := 99; RAISE EXCEPTION 'ERROR : %',errmsg; END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) OWNER TO postgres; But.. it's showing error in execute time .. ERROR: SPI_execute_plan failed executing query "ROLLBACK": SPI_ERROR_TRANSACTION

  Read the article

• postgres - ERROR: operator does not exist

  - by cino21122

  Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself... Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. The query is this: BEGIN; SELECT zip_proximity_sum('zc', (SELECT g.lat FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT g.lon FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT m.zip FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1) ,10); The PG function is this: CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance FROM geocoded g LEFT JOIN masterfile r ON g.recordid = r.id WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5) ORDER BY r.zip, distance; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;

  Read the article

• copy rows before updating them to preserve archive in Postgres

  - by punkish

  I am experimenting with creating a table that keeps a version of every row. The idea is to be able to query for how the rows were at any point in time even if the query has JOINs. Consider a system where the primary resource is books, that is, books are queried for, and author info comes along for the ride CREATE TABLE authors ( author_id INTEGER NOT NULL, version INTEGER NOT NULL CHECK (version > 0), author_name TEXT, is_active BOOLEAN DEFAULT '1', modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (author_id, version) ) INSERT INTO authors (author_id, version, author_name) VALUES (1, 1, 'John'), (2, 1, 'Jack'), (3, 1, 'Ernest'); I would like to be able to update the above like so UPDATE authors SET author_name = 'Jack K' WHERE author_id = 1; and end up with 2, 1, Jack, t, 2012-03-29 21:35:00 2, 2, Jack K, t, 2012-03-29 21:37:40 which I can then query with SELECT author_name, modified_on FROM authors WHERE author_id = 2 AND modified_on < '2012-03-29 21:37:00' ORDER BY version DESC LIMIT 1; to get 2, 1, Jack, t, 2012-03-29 21:35:00 Something like the following doesn't really work CREATE OR REPLACE FUNCTION archive_authors() RETURNS TRIGGER AS $archive_author$ BEGIN IF (TG_OP = 'UPDATE') THEN -- The following fails because author_id,version PK already exists INSERT INTO authors (author_id, version, author_name) VALUES (OLD.author_id, OLD.version, OLD.author_name); UPDATE authors SET version = OLD.version + 1 WHERE author_id = OLD.author_id AND version = OLD.version; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $archive_author$ LANGUAGE plpgsql; CREATE TRIGGER archive_author AFTER UPDATE OR DELETE ON authors FOR EACH ROW EXECUTE PROCEDURE archive_authors(); How can I achieve the above? Or, is there a better way to accomplish this? Ideally, I would prefer to not create a shadow table to store the archived rows.

  Read the article

• Creating a function in Postgresql that does not return composite values

  - by celenius

  I'm learning how to write functions in Postgresql. I've defined a function called _tmp_myfunction() which takes in an id and returns a table (I also define a table object type called _tmp_mytable) -- create object type to be returned CREATE TYPE _tmp_mytable AS ( id integer, cost double precision ); -- create function which returns query CREATE OR REPLACE FUNCTION _tmp_myfunction( id integer ) RETURNS SETOF _tmp_mytable AS $$ BEGIN RETURN QUERY SELECT id, cost FROM sales WHERE id = sales.id; END; $$ LANGUAGE plpgsql; This works fine when I use one id and call it using the following approach: SELECT * FROM _tmp_myfunction(402); What I would like to be able to do is to call it, but to use a column of values instead of just one value. However, if I use the following approach I end up with all values of the table in one column, separated by commas: -- call function using all values in a column SELECT _tmp_myfunction(t.id) FROM transactions as t; I understand that I can get the same result if I use SELECT _tmp_myfunction(402); instead of SELECT * FROM _tmp_myfunction(402); but I don't know how to construct my query in such a way that I can separate out the results.

  Read the article

• Breaking 1NF to model subset constraints. Does this sound sane?

  - by Chris Travers

  My first question here. Appologize if it is in the wrong forum but this seems pretty conceptual. I am looking at doing something that goes against conventional wisdom and want to get some feedback as to whether this is totally insane or will result in problems, so critique away! I am on PostgreSQL 9.1 but may be moving to 9.2 for this part of this project. To re-iterate: Does it seem sane to break 1NF in this way? I am not looking for debugging code so much as where people see problems that this might lead. The Problem In double entry accounting, financial transactions are journal entries with an arbitrary number of lines. Each line has either a left value (debit) or a right value (credit) which can be modelled as a single value with negatives as debits and positives as credits or vice versa. The sum of all debits and credits must equal zero (so if we go with a single amount field, sum(amount) must equal zero for each financial journal entry). SQL-based databases, pretty much required for this sort of work, have no way to express this sort of constraint natively and so any approach to enforcing it in the database seems rather complex. The Write Model The journal entries are append only. There is a possibility we will add a delete model but it will be subject to a different set of restrictions and so is not applicable here. If and when we allow deletes, we will probably do them using a simple ON DELETE CASCADE designation on the foreign key, and require that deletes go through a dedicated stored procedure which can enforce the other constraints. So inserts and selects have to be accommodated but updates and deletes do not for this task. My Proposed Solution My proposed solution is to break first normal form and model constraints on arrays of tuples, with a trigger that breaks the rows out into another table. CREATE TABLE journal_line ( entry_id bigserial primary key, account_id int not null references account(id), journal_entry_id bigint not null, -- adding references later amount numeric not null ); I would then add "table methods" to extract debits and credits for reporting purposes: CREATE OR REPLACE FUNCTION debits(journal_line) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT CASE WHEN $1.amount < 0 THEN $1.amount * -1 ELSE NULL END; $$; CREATE OR REPLACE FUNCTION credits(journal_line) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT CASE WHEN $1.amount > 0 THEN $1.amount ELSE NULL END; $$; Then the journal entry table (simplified for this example): CREATE TABLE journal_entry ( entry_id bigserial primary key, -- no natural keys :-( journal_id int not null references journal(id), date_posted date not null, reference text not null, description text not null, journal_lines journal_line[] not null ); Then a table method and and check constraints: CREATE OR REPLACE FUNCTION running_total(journal_entry) returns numeric language sql immutable as $$ SELECT sum(amount) FROM unnest($1.journal_lines); $$; ALTER TABLE journal_entry ADD CONSTRAINT CHECK (((journal_entry.running_total) = 0)); ALTER TABLE journal_line ADD FOREIGN KEY journal_entry_id REFERENCES journal_entry(entry_id); And finally we'd have a breakout trigger: CREATE OR REPLACE FUNCTION je_breakout() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO journal_line (journal_entry_id, account_id, amount) SELECT NEW.id, account_id, amount FROM unnest(NEW.journal_lines); RETURN NEW; ELSE RAISE EXCEPTION 'Operation Not Allowed'; END IF; END; $$; And finally CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON journal_entry FOR EACH ROW EXECUTE_PROCEDURE je_breaout(); Of course the example above is simplified. There will be a status table that will track approval status allowing for separation of duties, etc. However the goal here is to prevent unbalanced transactions. Any feedback? Does this sound entirely insane? Standard Solutions? In getting to this point I have to say I have looked at four different current ERP solutions to this problems: Represent every line item as a debit and a credit against different accounts. Use of foreign keys against the line item table to enforce an eventual running total of 0 Use of constraint triggers in PostgreSQL Forcing all validation here solely through the app logic. My concerns are that #1 is pretty limiting and very hard to audit internally. It's not programmer transparent and so it strikes me as being difficult to work with in the future. The second strikes me as being very complex and required a series of contraints and foreign keys against self to make work, and therefore it strikes me as complex, hard to sort out at least in my mind, and thus hard to work with. The fourth could be done as we force all access through stored procedures anyway and this is the most common solution (have the app total things up and throw an error otherwise). However, I think proof that a constraint is followed is superior to test cases, and so the question becomes whether this in fact generates insert anomilies rather than solving them. If this is a solved problem it isn't the case that everyone agrees on the solution....

  Read the article

• Postgres Stored procedure using iBatis

  - by Om Yadav

  --- The error occurred while applying a parameter map. --- Check the newSubs-InlineParameterMap. --- Check the statement (query failed). --- Cause: org.postgresql.util.PSQLException: ERROR: wrong record type supplied in RETURN NEXT Where: PL/pgSQL function "getnewsubs" line 34 at return next the function detail is as below.... CREATE OR REPLACE FUNCTION getnewsubs(timestamp without time zone, timestamp without time zone, integer) RETURNS SETOF record AS $BODY$declare v_fromdt alias for $1; v_todt alias for $2; v_domno alias for $3; v_cursor refcursor; v_rec record; v_cpno bigint; v_actno int; v_actname varchar(50); v_actid varchar(100); v_cpntypeid varchar(100); v_mrp double precision; v_domname varchar(100); v_usedt timestamp without time zone; v_expirydt timestamp without time zone; v_createdt timestamp without time zone; v_ctno int; v_phone varchar; begin open v_cursor for select cpno,c.actno,usedt from cpnusage c inner join account s on s.actno=c.actno where usedt = $1 and usedt < $2 and validdomstat(s.domno,v_domno) order by c.usedt; fetch v_cursor into v_cpno,v_actno,v_usedt; while found loop if isactivation(v_cpno,v_actno,v_usedt) IS TRUE then select into v_actno,v_actname,v_actid,v_cpntypeid,v_mrp,v_domname,v_ctno,v_cpno,v_usedt,v_expirydt,v_createdt,v_phone a.actno,a.actname as name,a.actid as actid,c.descr as cpntypeid,l.mrp as mrp,s.domname as domname,c.ctno as ctno,b.cpno,b.usedt,b.expirydt,d.createdt,a.phone from account a inner join cpnusage b on a.actno=b.actno inner join cpn d on b.cpno=d.cpno inner join cpntype c on d.ctno=c.ctno inner join ssgdom s on a.domno=s.domno left join price_class l ON l.price_class_id=b.price_class_id where validdomstat(a.domno,v_domno) and b.cpno=v_cpno and b.actno=v_actno; select into v_rec v_actno,v_actname,v_actid,v_cpntypeid,v_mrp,v_domname,v_ctno,v_cpno,v_usedt,v_expirydt,v_createdt,v_phone; return next v_rec; end if; fetch v_cursor into v_cpno,v_actno,v_usedt; end loop; return ; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION getnewsubs(timestamp without time zone, timestamp without time zone, integer) OWNER TO radius If i am running the function from the console it is running fine and giving me the correct response. But when using through java causing the above error. Can ay body help in it..Its very urgent. Please response as soon as possible. Thanks in advance.

  Read the article

< Previous Page | 1 2