insert select fails inside of function

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

On Fri, Jun 22, 2012 at 3:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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