[SQL] pl/pgsql, cursors and C function
Hi
I'm making my first steps in C functions. I want to avoid doing all the
SQL job in them, pl/pgsql looks a better choice. I tried to do this by
passing opened cursor from pl/pgsql function to C function.
Here is simple C function:
#include
#include
PG_FUNCTION_INFO_V1(test2);
Datum test2(PG_FUNCTION_ARGS)
{
Portal p;
int n;
p=SPI_cursor_find("xxx");
if(!p)
elog(ERROR,"Cursor error");
SPI_cursor_fetch(p,true,1);
n=SPI_processed;
PG_RETURN_INT32(n);
}
And pl/pgsql one:
CREATE OR REPLACE FUNCTION test() returns integer AS '
DECLARE
_m CURSOR FOR select id from some_table limit 1;
n integer;
BEGIN
_m=''xxx'';
open _m;
n=test2();
close _m;
return n;
END;
' language 'plpgsql';
select test();
I don't understand ERROR message at all:
ERROR: SPI_prepare() failed on "SELECT $1 "
This error is raised when trying to execute SPI_cursor_fetch. What does
it mean? What does the SPI_prepare have to already opened cursor?
Where can I find better SPI documentation than "Postgresql Server
Programming" ?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] pl/pgsql, cursors and C function
Tomasz Myrta <[EMAIL PROTECTED]> writes: > I don't understand ERROR message at all: > ERROR: SPI_prepare() failed on "SELECT $1 " This is a bug. Or two bugs, actually: one of yours and one of PG's. I have repaired the PG bug with the attached patch. The bug in your code is that your C function needs to call SPI_connect and SPI_finish if it's going to use any SPI operations. regards, tom lane Index: spi.c === RCS file: /cvsroot/pgsql-server/src/backend/executor/spi.c,v retrieving revision 1.75.2.3 diff -c -r1.75.2.3 spi.c *** spi.c 14 Feb 2003 21:12:54 - 1.75.2.3 --- spi.c 23 Sep 2003 15:09:39 - *** *** 1387,1393 elog(ERROR, "invalid portal in SPI cursor operation"); /* Push the SPI stack */ ! _SPI_begin_call(true); /* Reset the SPI result */ SPI_processed = 0; --- 1387,1394 elog(ERROR, "invalid portal in SPI cursor operation"); /* Push the SPI stack */ ! if (_SPI_begin_call(true) < 0) ! elog(ERROR, "SPI cursor operation called while not connected"); /* Reset the SPI result */ SPI_processed = 0; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] updating a field with a SUM from another table
My SQL is apparently a bit rusty, can anyone advise how to refactor this updatecharges query to work? I need to update a total-charges field in my orders table with the sum of the line-item charges in another table. The tables are related by the orderid column. Thanks for any help you can provide with updatecharges. Query: updatecharges (query I'm having trouble with) UPDATE orders RIGHT JOIN orderchargetotals ON orders.orderid = orderchargetotals.orderid SET orders.chargeasbilled = orderchargetotals.orderchargeasbilled; Query: orderchargetotals SELECT ordercharges.orderid, SUM(ordercharges.orderchargeasbilled) AS orderchargeasbilled FROM ordercharges GROUP BY orderid ORDER BY orderid; Table: orders - orderid, chargeasbilled, field1, field2, ... Table: ordercharges --- orderchargeid, orderid, chargecode, ordercharge BTW, I do know its counter to relational precepts to store a total one could calculate at query-time, but I have reasons to do so at the the moment. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] pl/pgsql, cursors and C function
I don't understand ERROR message at all: ERROR: SPI_prepare() failed on "SELECT $1 " > This is a bug. Or two bugs, actually: one of yours and one of PG's. I have repaired the PG bug with the attached patch. The bug in your code is that your C function needs to call SPI_connect and SPI_finish if it's going to use any SPI operations. regards, tom lane Thanks a lot. The first answer helped me, the second one - the others. Do you know anything about good source of C functions documentation and examples? Currently I'm walking over .h headers to find some useful functions and macros. Regads, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Using sql statements in file
On Mon, 22 Sep 2003, Suresh Basandra wrote: > Hi, > > I would like to do the following using files: > > 1. put create database, create tables sql statements in a file and > execute through prompt > 2. insert or update data that is put in a file > > Please let me know if there are any examples that lists how sql > statements can be put in a file and used. psql dbnamehere http://archives.postgresql.org
Re: [SQL] pl/pgsql, cursors and C function
Tomasz Myrta <[EMAIL PROTECTED]> writes: > Do you know anything about good source of C functions documentation and > examples? Look in the main sources (backend/utils/adt/, mostly) and/or contrib modules for functions that do something like what you need. The only difference between a builtin function and a dynamically loaded one is you have to add the PG_FUNCTION_INFO macro. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selecting duplicate records
This is a test I did on one of my tables where I put duplicated entries : select * from acct_other_2003sep except select sep.* from ( select min(oid) as min_oid, "Acct-Status-Type", "User-Name", "Realm", "Acct-Session-Time", "Acct-Input-Octets", "Acct-Output-Octets", "Called-Station-Id", "Calling-Station-Id", "Acct-Terminate-Cause", "Framed-IP-Address", "Service-Type", "Framed-Protocol", "Client-IP-Address", "NAS-IP-Address", "NAS-Port-Type", "NAS-Port-Id", "Acct-Session-Id", "Acct-Link-Count", "Acct-Multi-Session-Id" from acct_other_2003sep group by "Acct-Status-Type", "User-Name", "Realm", "Acct-Session-Time", "Acct-Input-Octets", "Acct-Output-Octets", "Called-Station-Id", "Calling-Station-Id", "Acct-Terminate-Cause", "Framed-IP-Address", "Service-Type", "Framed-Protocol", "Client-IP-Address", "NAS-IP-Address", "NAS-Port-Type", "NAS-Port-Id", "Acct-Session-Id", "Acct-Link-Count", "Acct-Multi-Session-Id" ) as min_sep, acct_other_2003sep as sep where sep.oid = min_sep.min_oid ; From the above example you can see how to use a subselect to get a unique list then using except, you can get the records that were not unique. This may not be exactly what you want but it does implement some of the methods required to get around using using temporary tables. For some tasks using temporary tables may be more suitable if your query becomes too complex and or you run out of memory/time. Hope this helps. Guy Christopher Browne wrote: The world rejoiced as [EMAIL PROTECTED] (Christoph Haller) wrote: 1. How to select duplicate records only from a single table using a select query. e.g. select sid,count(sid) from location group by sid having count(sid)>1; Do you get the idea? Your request is pretty unspecific, so if this is not what you're asking for, try again. The aggregate is likely to perform horrifically badly. Here might be an option: Step 1. Find all of the duplicates... select a.* into temp table sid from some_table a, some_table b where a.oid < b.oid and a.field1 = b.field1 and a.field2 = b.field2 and a.field3 = b.field3 and ... a.fieldn = b.fieldn; Step 2. Look for the matching entries in the source table... select a.* from some_table a, sid b where a.field1 = b.field1 and a.field2 = b.field2 and a.field3 = b.field3 and ... a.fieldn = b.fieldn; [There's a weakness here; if there are multiple dupes, they may get picked multiple times in the second query :-(.] -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 8: explain analyze is your friend
