From: | Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: insert select fails inside of function |
Date: | 2012-06-22 20:11:10 |
Message-ID: | CAD+mzowd3HC1Vbp2ZHr6N+eSyVpTVS-Q=N7UB1AbJVx_9eVUJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-22 21:09:42 | Re: insert select fails inside of function |
Previous Message | Tom Lane | 2012-06-22 19:44:15 | Re: insert select fails inside of function |