[GENERAL] Problem writing function

[Christian Rengstl]

Hi list,

the following function is created properly: 
CREATE OR REPLACE FUNCTION insert_into_table_overview(text, chr integer)
RETURNS void AS '
DECLARE
in_tableALIAS FOR $1;
p RECORD;
BEGIN
RAISE NOTICE ''in_table = %'', in_table;
FOR p IN EXECUTE ''select distinct pid from '' ||
quote_ident(in_table) LOOP
EXECUTE ''insert into table_overview(table_name, chr,
start_no, end_no, pid)
select '' || quote_ident(tname) || '', chr,
min(entry_no), max(entry_no), p from '' || quote_ident(in_table);
END LOOP;
END;
' LANGUAGE plpgsql;

But when i execute it with select
insert_into_table_overview('test1'::text, 1); i only get the following
output:
NOTICE:  in_table = test1

ERROR:  relation test1 does not exist
CONTEXT:  SQL statement select distinct pid from test1
PL/pgSQL function insert_into_table_overview line 6 at for over
execute statement

I am sure that there is something wrong with the quotes, but i just
can't find out what.

Chris




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Problem writing function

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-06 11:47:43 +0200:
 the following function is created properly: 
 CREATE OR REPLACE FUNCTION insert_into_table_overview(text, chr integer)
 RETURNS void AS '
 DECLARE
 in_tableALIAS FOR $1;
 p RECORD;
 BEGIN
   RAISE NOTICE ''in_table = %'', in_table;
   FOR p IN EXECUTE ''select distinct pid from '' ||
 quote_ident(in_table) LOOP
   EXECUTE ''insert into table_overview(table_name, chr,
 start_no, end_no, pid)
   select '' || quote_ident(tname) || '', chr,

should the tname be in_table?

 min(entry_no), max(entry_no), p from '' || quote_ident(in_table);
   END LOOP;
 END;
 ' LANGUAGE plpgsql;
 
 But when i execute it with select
 insert_into_table_overview('test1'::text, 1); i only get the following
 output:
 NOTICE:  in_table = test1
 
 ERROR:  relation test1 does not exist
 CONTEXT:  SQL statement select distinct pid from test1
 PL/pgSQL function insert_into_table_overview line 6 at for over
 execute statement
 
 I am sure that there is something wrong with the quotes, but i just
 can't find out what.

Is there a table called test1?

SELECT * FROM test1;
SELECT * FROM test1;

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster