[SQL] Using Temporary Tables in postgres functions
When I try to use TEMPORARY TABLE within postgres functions (using 'sql' as a function language), I can't because postgres can't find that temporary table. Consider this example: CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$ CREATE TEMPORARY TABLE tmpTbl AS SELECT message_id FROM cached_messages WHERE billing_status = 2; UPDATE cached_messages SET billing_status = 1 WHERE message_id IN (SELECT message_id FROM tmpTbl); SELECT * FROM v_messages_full WHERE message_id IN (SELECT message_id FROM tmpTbl); $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; When I try to execute above CREATE FUNCTION statement postgres gives me this: ERROR: relation "tmptbl" does not exist CONTEXT: SQL function "func1" If I rewrite func1() using 'plpgsq' I have no troubles creating temporary tables, I just need to use EXEC when referencing to those temporary tables (which is cumbersome, but there it is). Am I doing something wrong here, or there is no way of using temporary tables within 'sql' written functions? Mike ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote: > When I try to use TEMPORARY TABLE within postgres functions (using 'sql' > as a function language), I can't because postgres can't find that > temporary table. Consider this example: You need to build the temp table and EXECUTE the statement. The problem is that the plan is cached for later re-use. Since the cached plan has the id of a table that no longer exists, you get this error. See the PL/pgSQL part of the manual for more on this. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, 25 Jan 2007, Mario Splivalo wrote: > When I try to use TEMPORARY TABLE within postgres functions (using 'sql' > as a function language), I can't because postgres can't find that > temporary table. Consider this example: > > CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$ > CREATE TEMPORARY TABLE tmpTbl > AS > SELECT > message_id > FROM > cached_messages > WHERE > billing_status = 2; > > > UPDATE cached_messages SET billing_status = 1 WHERE message_id IN > (SELECT message_id FROM tmpTbl); > > SELECT > * > FROM > v_messages_full > WHERE > message_id IN (SELECT message_id FROM tmpTbl); > $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; It seems like the sql function checker is unhappy with the above. Does it actually work if you turn off the check_function_bodies configuration variable, create the function and then call it? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using Temporary Tables in postgres functions
Mario Splivalo <[EMAIL PROTECTED]> writes: > Am I doing something wrong here, or there is no way of using temporary > tables within 'sql' written functions? I believe the problem is that for a SQL function we parse the whole function body before executing any of it. So you'd need to split this into two separate functions. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Looping recordset
Hello, How can I loop a PL/PgSQL recorset variable? The example: DECLARE v_tmp_regi RECORD; v_tmp RECORD; BEGIN SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE sf.termekfajta_id= a_termekfajta_id AND sf.marka_id=a_marka_id; DELETE FROM sulyozas_futamido; FOR v_tmp IN v_tmp_regi LOOP --I would like to work here with the old recordset! END LOOP; ^^ -- This is not working !!! END; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] shared_buffers and shmall,shmmax
> shmall is 65536 page And how big is a page? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] shared_buffers and shmall,shmmax
Tom, I am trying to tunrn my db server (p4 3.2GHzCPU, 2GB RAM on FreeBSD 5.5, postgresql 8.1.4), I am trying to increase the share_buffer to 512M 1/4 of RAM, so I set it to shared_buffers=65536 (512M), then failed to start, then I reduce to 6,4,3, it failed to start database until 2. Following is my kernel: kern.ipc.maxsockbuf: 262144 kern.ipc.sockbuf_waste_factor: 8 kern.ipc.somaxconn: 128 kern.ipc.semaem: 16384 kern.ipc.semvmx: 32767 kern.ipc.semusz: 344 kern.ipc.semume: 40 kern.ipc.semopm: 100 kern.ipc.semmsl: 240 kern.ipc.semmnu: 120 kern.ipc.semmns: 240 kern.ipc.semmni: 40 kern.ipc.semmap: 30 kern.ipc.shm_allow_removed: 0 kern.ipc.shm_use_phys: 0 kern.ipc.shmall: 65536 kern.ipc.shmseg: 128 kern.ipc.shmmni: 192 kern.ipc.shmmin: 1 kern.ipc.shmmax: 536870912 kern.ipc.numopensockets: 117 kern.ipc.maxsockets: 25600 shmmax is 512M shmall is 65536 page following is my pgsql.log: Jan 25 09:52:09 iguard postgres[49577]: [7-1] FATAL: terminating connection due to administrator command Jan 25 09:52:09 iguard postgres[46784]: [15-1] FATAL: terminating connection due to administrator command Jan 25 09:52:09 iguard postgres[49439]: [12-1] FATAL: terminating connection due to administrator command Jan 25 09:54:00 iguard postgres[49909]: [1-3] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. Jan 25 09:54:00 iguard postgres[49909]: [1-4] You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently Jan 25 09:54:00 iguard postgres[49909]: [1-5] 607731712 bytes), reduce PostgreSQL's shared_buffers parameter (currently 65535) and/or its max_connections parameter Jan 25 09:54:00 iguard postgres[49909]: [1-6] (currently 120). Jan 25 09:54:00 iguard postgres[49909]: [1-7] If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising Jan 25 09:54:00 iguard postgres[49909]: [1-8] the request size or reconfiguring SHMMIN is called for. Jan 25 09:54:00 iguard postgres[49909]: [1-9] The PostgreSQL documentation contains more information about shared memory configuration. Jan 25 09:54:31 iguard postgres[49919]: [1-1] FATAL: could not create shared memory segment: Invalid argument Jan 25 09:54:31 iguard postgres[49919]: [1-2] DETAIL: Failed system call was shmget(key=5432001, size=561455104, 03600). Jan 25 09:54:31 iguard postgres[49919]: [1-3] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. Jan 25 09:54:31 iguard postgres[49919]: [1-4] You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently Jan 25 09:54:31 iguard postgres[49919]: [1-5] 561455104 bytes), reduce PostgreSQL's shared_buffers parameter (currently 6) and/or its max_connections parameter Jan 25 09:54:31 iguard postgres[49919]: [1-6] (currently 120). Jan 25 09:54:31 iguard postgres[49919]: [1-7] If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising Jan 25 09:54:31 iguard postgres[49919]: [1-8] the request size or reconfiguring SHMMIN is called for. Jan 25 09:54:31 iguard postgres[49919]: [1-9] The PostgreSQL documentation contains more information about shared memory configuration. Jan 25 09:55:00 iguard postgres[49929]: [1-1] FATAL: could not create shared memory segment: Cannot allocate memory Jan 25 09:55:00 iguard postgres[49929]: [1-2] DETAIL: Failed system call was shmget(key=5432001, size=394256384, 03600). Jan 25 09:55:00 iguard postgres[49929]: [1-3] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. Jan 25 09:55:00 iguard postgres[49929]: [1-4] To reduce the request size (currently 394256384 bytes), reduce PostgreSQL's shared_buffers parameter (currently 4) and/or Jan 25 09:55:00 iguard postgres[49929]: [1-5] its max_connections parameter (currently 120). Jan 25 09:55:00 iguard postgres[49929]: [1-6] The PostgreSQL documentation contains more information about shared memory configuration. Jan 25 09:55:34 iguard postgres[49953]: [1-1] FATAL: could not create shared memory segment: Cannot allocate memory Jan 25 09:55:34 iguard postgres[49953]: [1-2] DETAIL: Failed system call was shmget(key=5432001, size=310394880, 03600). Jan 25 09:55:34 iguard postgres[49953]: [1-3] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. Jan 25 09:55:34 iguard postgres[49953]: [1-4] To reduce the request size (currently 310394880 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3) and/or Jan 25 09:55:34 iguard postgres[49953]: [1-5] its max_connections parameter (currently 120). Jan 25 09:55:34 iguard postgres[49953]: [1-6] The PostgreS
Re: [SQL] shared_buffers and shmall,shmmax
Tom, Sorry, it's 4kb, that explain it. Thank you, Regards, Jie Liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, January 25, 2007 12:15 PM To: Jie Liang Cc: pgsql-sql@postgresql.org Subject: Re: shared_buffers and shmall,shmmax > shmall is 65536 page And how big is a page? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] LEFT Join Question
Hello all, Ive been racking my brain for a few hours now and need some help, please!!! I have the following tables : account =account_id =account_type_id =account_username vendor =account_id =vendor_status contact_name =account_id =name =domain_type_id contact_address =account_id =address =domain_type_id contact_email =account_id =email =domain_type_id contact_phone =account_id =phone =domain_type_id account_type =account_type_id = account_type_tag records : 1 VENDOR 2 SELLER 3 CONTRACTOR domain_type =domain_type_id =domain_type_tag records : 1 PRIMARY 2 SECONDARY Im looking for a select that will give me all records where the account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY" even if the address, email and phone are blank. Users WILL HAVE records in the following tables : account, vendor contact_name contact_email they MAY or MAYNOT have records in the following tables : contact_address contact_phone I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. Im basically looking for this : account_username vendor_status name address email phone - Rob123 ACTIVE ROB 123 Test Drive[EMAIL PROTECTED]555-1212 BILL123 ACTIVE Bill NULL [EMAIL PROTECTED] 456- Steve1234 INACTIVE Steve 654 Hill St [EMAIL PROTECTED] NULL I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. (do I still need the = in the where clause when using a left join?) SELECT account.account_id, account.account_username, vendor.vendor_status, contact_name.name, contact_address.address, contact_email.email, contact_phone.phone FROM account a LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id) LEFT JOIN contact_address on (contact_address.account_id = a.account_id), vendor, contact_email, contact_name, domain_type, account_type, WHERE vendor.vendor_id = account.account_id AND contact_email.account_id = account.account_id AND contact_name.account_id = account.account_id AND account.account_type_id = account_type.account_type_id AND contact_email.domain_type_id = domain_type.domain_type_id AND contact_name.domain_type_id = domain_type.domain_type_id AND vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND account_type.account_type_tag = 'ACCOUNT_VENDOR' AND domain_type.domain_type_tag = 'VENDOR_PRIMARY' The problem Im having is b/c Im only looking for specific domain types I have a join on the contact address and phone tables where the domain types match - but if there are no records - it causes the entire record not to be show - so I need to do a left join on that table as well but If I try to do it - I get an error " table name "contact_phone" specified more than once" Any help would be great!!! Thanks -Rob
Re: [SQL] LEFT Join Question
Just 1 followup to this : they MAY or MAYNOT have records in the following tables : contact_address,contact_phone There may also be multiple records in that table w/ the same account_id - but the domain_type_id will be different. so the contact_phone could have 2 records : account_id 1, domain PRIMARY phone 555-1212 account_id 1, domain SECONDARY phone -123-4556
Re: [SQL] Looping recordset
DECLARE v_tmp_regi RECORD; v_tmp RECORD; BEGIN SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE sf.termekfajta_id= a_termekfajta_id AND sf.marka_id=a_marka_id; DELETE FROM sulyozas_futamido; FOR v_tmp IN v_tmp_regi LOOP --I would like to work here with the old recordset! END LOOP; ^^ -- This is not working !!! I'd say v_tmp_regi is a record variable, it is supposed to hold one record, not a set of records. Depending on the purpose, I'd probably use a temporary table to hold the selected rows or, perhaps, use the SELECT in the FOR..LOOP and subsequently perform the DELETE. Hélder M. Vieira ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] LEFT Join Question
Fisrt -- you probably want to start by doing fully qualified JOINS and then you want to allow joins with nulls on the columns that are allowed to be empty: I am doing this sort of off the top of my head ... but the thing you need to do generally is to COMPLETELY QUALIFY all of your joins and then use the "OR field IS NULL" trick. That should solve your problem. SELECT A.account_id, A.account_username, V.vendor_status,CN.name, CA.address,CE.email, CP.phone FROM account A LEFT JOIN contact_phone CP ON (CP.account_id = A.account_id OR CP.account_id IS NULL) LEFT JOIN contact_address CA ON (CA.account_id = A.account_id OR CA.account_id IS NULL), JOIN vendor V ON (V.account_id = A.account_id), JOIN contact_email CE ON (CE.account_id = A.account_id OR CE.account_id IS NULL), JOIN contact_name CN ON (CN.account_id = A.account_id), JOIN domain_type DT ON (CE.domain_type_id = DT.domain_type_id ), JOIN account_type AT ON (AT.account_type_id = A..account_type_id) HAVING A.account_type_tag = 'ACCOUNT_VENDOR' AND DT.domain_type_tag = 'VENDOR_PRIMARY' ""Rob V"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hello all, Ive been racking my brain for a few hours now and need some help, please!!! I have the following tables : account =account_id =account_type_id =account_username vendor =account_id =vendor_status contact_name =account_id =name =domain_type_id contact_address =account_id =address =domain_type_id contact_email =account_id =email =domain_type_id contact_phone =account_id =phone =domain_type_id account_type =account_type_id = account_type_tag records : 1 VENDOR 2 SELLER 3 CONTRACTOR domain_type =domain_type_id =domain_type_tag records : 1 PRIMARY 2 SECONDARY Im looking for a select that will give me all records where the account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY" even if the address, email and phone are blank. Users WILL HAVE records in the following tables : account, vendor contact_name contact_email they MAY or MAYNOT have records in the following tables : contact_address contact_phone I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. Im basically looking for this : account_username vendor_status name address email phone - Rob123 ACTIVE ROB 123 Test Drive[EMAIL PROTECTED]555-1212 BILL123 ACTIVE Bill NULL [EMAIL PROTECTED] 456- Steve1234 INACTIVE Steve 654 Hill St [EMAIL PROTECTED] NULL I know I have to use a left join - but I can seem to figure out the syntax when dealing w/ different columns of the same table. (do I still need the = in the where clause when using a left join?) SELECT account.account_id, account.account_username, vendor.vendor_status, contact_name.name, contact_address.address, contact_email.email, contact_phone.phone FROM account a LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id) LEFT JOIN contact_address on (contact_address.account_id = a.account_id), vendor, contact_email, contact_name, domain_type, account_type, WHERE vendor.vendor_id = account.account_id AND contact_email.account_id = account.account_id AND contact_name.account_id = account.account_id AND account.account_type_id = account_type.account_type_id AND contact_email.domain_type_id = domain_type.domain_type_id AND contact_name.domain_type_id = domain_type.domain_type_id AND vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND account_type.account_type_tag = 'ACCOUNT_VENDOR' AND domain_type.domain_type_tag = 'VENDOR_PRIMARY' The problem Im having is b/c Im only looking for specific domain types I have a join on the contact address and phone tables where the domain types match - but if there are no records - it causes the entire record not to be show - so I need to do a left join on that table as well but If I try to do it - I get an error " table name "contact_phone" specified more than once" Any help would be great!!! Thanks -Rob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
