Thread: Problem writing function

Hi list,

the following function is created properly:
CREATE OR REPLACE FUNCTION insert_into_table_overview(text, chr integer)
RETURNS void AS '
DECLARE
        in_table                ALIAS 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

# Christian.Rengstl@klinik.uni-regensburg.de / 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_table                ALIAS 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