Re: insert select fails inside of function

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)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
>