Re: Problem with insert

Tom Lane wrote:
>
> Jerome Raupach <jraupach(at)intelcom(dot)fr> writes:
> > The query:
> > INSERT INTO table_resultat( origine, service, noeud, rubrique,
> > nb_passage, temps, date)
> > SELECT DISTINCT temp2.origine, temp2.service, temp2.noeud,
> > temp2.rubrique, temp2.nb_passage, temp2.temps, temp2.date FROM temp2
> > WHERE not exists
> > ( SELECT table_resultat.origine, table_resultat.service,
> > table_resultat.noeud, table_resultat.rubrique, table_resultat.date FROM
> > table_brut WHERE table_resultat.origine=temp2.origine AND
> > table_resultat.service=temp2.service AND
> > table_resultat.noeud=temp2.noeud AND
> > table_resultat.rubrique=temp2.rubrique AND
> > table_resultat.date=temp2.date )
>
> > produces the error :
> > ERROR: replace_vars_with_subplan_refs: variable not in subplan target
> > list
>
> That's pretty interesting. I was not able to reproduce this failure
> using stripped-down table definitions --- I tried
>
> create table foo (f1 int);
> create table bar (f1 int);
> create table baz (f1 int);
>
> insert into foo(f1)
> select distinct f1 from bar
> where not exists (select foo.f1 from baz where
> foo.f1 = bar.f1);
>
> So I think there must be some special feature of your tables that you
> haven't shown us. Could we see a schema dump (pg_dump -s) for these
> tables?
>
> BTW the inner select seems pretty weird --- what is the point of joining
> against table_brut when you're not using it? But that doesn't look like
> it could provoke this error.
>
> regards, tom lane

the error is produced if temp2 is a view. If temp2 is a table, there is
no problem.

?

Thanks. Jerome.