[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 messag

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

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$ >

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 in

[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_fu

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

[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 2000

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

[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 =d

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 PR

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

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 the