headers
Detlef Jockheck | 2 Mar 2003 13:49
Picon

Doing multiple steps at once

Hi,

Basis: I have two tables (a & b). 
1st: I create/clear a temporary table c
2nd: doing a "insert into c select something from a"
3rd: doing a "insert into c select something from b"
4th: calculate a result with "select something from c group by column"
5th: go back to 1st after some checks/corrections in a.

Is it possible to do the four steps (1-4) at once (with a function, procedure 
or so?) 

ciao
Detlef

--

-- 
# Dipl. Ing. (FH) Detlef Jockheck 
# E-mail: detlef <at> jockheck.de
# -------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
Permalink | Reply | 1 comment |
headers
Lincoln Yeoh | 2 Mar 2003 14:26
Picon

Re: automatic creation of oid

At 09:10 PM 2/28/03 +0100, Detlef Jockheck wrote:
>I'm new to postgresql. How can automatically create unique object identifyer
>during insertion of new data. I 've seen that a can use a sequence, but I
>think there must be another way?

There are other options. But if you don't have any other requirements than 
what you mentioned, you should use sequence/serial.

Regards,
Link.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
Permalink | Reply | 0 comments |
headers
Mark Cave-Ayland | 2 Mar 2003 18:22
Picon

plpgsql memory leak in 7.3.2? (repost)

Not sure this made it to the list the first time around, apologies to
anyone who received it twice.

Mark.

-----Original Message-----
From: Mark Cave-Ayland [mailto:m.cave-ayland <at> webbased.co.uk] 
Sent: 01 March 2003 21:10
To: 'pgsql-general <at> postgresql.org'
Subject: plpgsql memory leak in 7.3.2?

Hi everyone,

Whilst continuing work on some of our large tables again, we've
encountered what we think may be a memory leak in plpgsql in 7.3.2. The
process to recreate the problem we are experiencing is given below:

1. First create a table with a million or so rows and create an index on
it

create table restable (
	resid int8
	);

create or replace function t_pop() returns int as '
declare
	i int8;
	sql varchar;
begin
	i := 0;
(Continue reading)

Permalink | Reply | 1 comment |
headers
Justin Clift | 2 Mar 2003 18:44
Favicon

Ok, we just need 26 more votes to win this award....

Hi everyone,

There is a voting poll for "Database of the Year" at LinuxQuestions.org. 
  Today/tonight is the last few hours for voting and we're only 26 votes 
away from winning.

If you don't mind registering on another site in order to vote, it would 
be really good to vote for PostgreSQL here.

"Database of the Year" Voting area:

http://www.linuxquestions.org/questions/showthread.php?s=6d2969d632ec9bd8866ddceb84677b00&threadid=39870

Present results:

http://www.linuxquestions.org/questions/poll.php?s=6d2969d632ec9bd8866ddceb84677b00&action=showresults&pollid=168

:-)

Regards and best wishes,

Justin Clift

--

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

---------------------------(end of broadcast)---------------------------
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tom Lane | 2 Mar 2003 19:10
Picon

Re: Doing multiple steps at once

Detlef Jockheck <detlef <at> jockheck.de> writes:
> 1st: I create/clear a temporary table c
> 2nd: doing a "insert into c select something from a"
> 3rd: doing a "insert into c select something from b"
> 4th: calculate a result with "select something from c group by column"

> Is it possible to do the four steps (1-4) at once (with a function, procedure 
> or so?) 

Do you need a temp table at all?  The given calculation could be done
with something like

	SELECT whatever
	FROM (SELECT something FROM a
	      UNION ALL
	      SELECT something FROM b) ss
	GROUP BY column

I can't see a need for a temp table unless your intention is to scan the
UNION result multiple times, in which case building the temp table might
be faster than repeating the UNION.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)
Permalink | Reply | 0 comments |
headers
Ben-Nes Michael | 2 Mar 2003 19:30
Picon

function problem plpgsql

Hi All

The following function used to work before I upgraded to 7.3.2 from 7.3.1

from some reason the delete don't delete anything :(

maybe something wrong with the delete statement ?
Can I use GET DIAGNOSTICS with the DELETE statement ?

I tried to run it by hand and it was successful.

Thanks in advance

CREATE FUNCTION forum_delete_topic(INT) RETURNS INTEGER AS '
DECLARE
return_value INTEGER;
lft_rgt forum_tree%ROWTYPE;
v_node_id ALIAS FOR $1;

BEGIN
SELECT INTO lft_rgt lft, rgt, f_id FROM forum_tree WHERE node_id =
v_node_id;

DELETE FROM forum_tree WHERE lft BETWEEN lft_rgt.lft AND lft_rgt.rgt AND
f_id = lft_rgt.f_id;

GET DIAGNOSTICS return_value = ROW_COUNT;

IF return_value < 1 THEN
 RETURN 0;
(Continue reading)

Permalink | Reply | 2 comments |
headers
Mark Tessier | 2 Mar 2003 19:48

pg_relcheck

I created a field and then decided to drop it. Postgres won't let me drop it, however. I get the following
error message when trying to drop it using phppgadmin:

Error - /home/httpd/htdocs/pgadmin/lib.inc.php -- Line: 718

PostgreSQL said: ERROR: Relation "pg_relcheck" does not exist
Your query:

SELECT
rcname as index_name,
rcsrc
FROM
pg_relcheck,
pg_class bc
WHERE
rcrelid = bc.oid
and bc.relname = 'client'
and not exists
(select * from pg_relcheck as c, pg_inherits as i
where i.inhrelid = pg_relcheck.rcrelid
and c.rcname = pg_relcheck.rcname
and c.rcsrc = pg_relcheck.rcsrc
and c.rcrelid = i.inhparent)

Could someone please explain why I am getting this error message.

--

-- 
Thanks,

Mark
(Continue reading)

Permalink | Reply | 1 comment |
headers
sector119 | 2 Mar 2003 20:23
Picon

does tsearch work with utf-8?

subject

--

-- 
WBR, sector119
Permalink | Reply | 0 comments |
headers
Tom Lane | 2 Mar 2003 21:51
Picon

Re: plpgsql memory leak in 7.3.2? (repost)

"Mark Cave-Ayland" <m.cave-ayland <at> webbased.co.uk> writes:
> Whilst continuing work on some of our large tables again, we've
> encountered what we think may be a memory leak in plpgsql in 7.3.2.

Yup, you're right.  Looks like I introduced several memory leaks in
plpgsql when I modified spi.c to return a tuple descriptor even with
zero tuples returned: some plpgsql routines assumed they didn't need
to do SPI_freetuptable() after retrieving no tuples.  Patch attached,
or you can grab the updated pl_exec.c from our CVS server.

			regards, tom lane

*** src/pl/plpgsql/src/pl_exec.c~	Tue Jan 21 17:06:36 2003
--- src/pl/plpgsql/src/pl_exec.c	Sun Mar  2 15:45:59 2003
***************
*** 1369,1379 ****
  			if (rc != PLPGSQL_RC_OK)
  			{
  				/*
! 				 * We're aborting the loop, so cleanup and set FOUND
  				 */
- 				exec_set_found(estate, found);
  				SPI_freetuptable(tuptab);
  				SPI_cursor_close(portal);

  				if (rc == PLPGSQL_RC_EXIT)
  				{
--- 1369,1380 ----
  			if (rc != PLPGSQL_RC_OK)
  			{
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tom Lane | 2 Mar 2003 21:53
Picon

Re: pg_relcheck

Mark Tessier <mt <at> open2web.com> writes:
> Could someone please explain why I am getting this error message.

Probably you are using an obsolete version of phppgadmin.
The pg_relcheck catalog doesn't exist anymore in 7.3.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 0 comments |

Gmane