Thread: insert select fails inside of function

Hi All,

I am scratching my head over this one. I have a basic function which populates a table inside of it (declared outside of the function but that shouldn't matter)  and it requires something like

insert into table1 (col1, ...)
select * from foo

and I get the error "query has no destination for result data". This is surely not the case as it's going directly into the insert statement which doesn't have output. Any idea how to get around this particularly annoying problem? I have to say, making the switch from t-sql to postgresql has been fairly easy except for function debugging. The inability to return a select statement from a function and have it display has caused hours of wasted time and debugging headaches. Is this going to change any time soon?

Thanks,
~Ben

Benedict Holland <benedict.m.holland@gmail.com> writes:
> I am scratching my head over this one. I have a basic function which
> populates a table inside of it (declared outside of the function but that
> shouldn't matter)  and it requires something like

> insert into table1 (col1, ...)
> select * from foo

> and I get the error "query has no destination for result data". This is
> surely not the case as it's going directly into the insert statement which
> doesn't have output. Any idea how to get around this particularly annoying
> problem?

What PG version?  Could we see the exact text of the function?  I'm
wondering about aliasing problems stemming from function variables named
similarly to the target table or its columns --- newer PG versions are
brighter about that sort of conflict than older ones.

> I have to say, making the switch from t-sql to postgresql has been
> fairly easy except for function debugging.

RAISE NOTICE is the usual substitute for what I think you were doing on
t-sql.  Also, I think EDB is still supporting their plpgsql debugger, so
you might consider experimenting with that.

            regards, tom lane

Hi Tom,

Thanks for the response. The PG version is 9.0. I can't really give you the text of the function unfortunately. I know though that there isn't any aliasing issues occurring. After commenting out all but one line, I have it down to, not a insert select but a

create temp table t1(id, ...) as
select (a few columns with names that don't match)
where etc.

As by the exact syntax specified http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html:

Tip: Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.

This is the only thing still left uncommented in the function.

Thanks,
~Ben

Benedict Holland <benedict.m.holland@gmail.com> writes:
> Thanks for the response. The PG version is 9.0. I can't really give you the
> text of the function unfortunately. I know though that there isn't any
> aliasing issues occurring. After commenting out all but one line, I have it
> down to, not a insert select but a

> create temp table t1(id, ...) as
> select (a few columns with names that don't match)
> where etc.

Hm.  A CREATE TABLE AS SELECT case works for me in 9.0.8.  Perhaps you
could sanitize what you've got into a small self-contained test case?

            regards, tom lane