Skip site navigation (1) Skip section navigation (2)

replace_vars_with_subplan_refs

From: Michal Schwarz <misch(at)czechin(dot)cz>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: replace_vars_with_subplan_refs
Date: 2002-04-16 15:18:47
Message-ID: ECF6A65C93D5D311A22C004F4909B35D1B4246@czechin-server.brno.czechin.cz (view raw or flat)
Thread:
Lists: pgsql-bugs
Problem:
--------
SELECT * FROM <view> returns following error message:
ERROR:  replace_vars_with_subplan_refs: variable not in subplan target list

Environment:
------------
Version: PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.2
System: Linux jtam.brno.czechin.cz 2.2.17-RAID #8 SMP Mon Oct 16 10:48:13
CEST 2000 i686 unknown

Exact steps to reproduce this bug:
----------------------------
CREATE TABLE "fs_ucastnici" (
	"id" integer DEFAULT nextval('"fs_ucastnici_id_seq"'::text) NOT
NULL,
	"jmeno" character varying(50),
	"email" character varying(40),
	Constraint "fs_ucastnici_pkey" Primary Key ("id")
);
CREATE SEQUENCE "fs_ucastnici_id_seq" start 1 increment 1 maxvalue
2147483647 minvalue 1 cache 1;

CREATE TABLE "userdb" (
	"username" character varying(16) NOT NULL,
	"email" text,
	"name" character varying(80)
);
CREATE UNIQUE INDEX userdb_pkey ON userdb USING btree (username);

CREATE TABLE "emaily" (
	"email" text NOT NULL,
	"jmeno" text,
	"akce" character varying(40) NOT NULL,
	Constraint "emaily_pkey" Primary Key ("email")
);

CREATE VIEW "infomail" as 
	SELECT DISTINCT ON (lower(vsichni.email)) vsichni.email,
vsichni.jmeno, vsichni.id, vsichni.odkud FROM (
		SELECT fs_ucastnici.email, fs_ucastnici.jmeno,
fs_ucastnici.id::text AS id, 'soutez' AS odkud FROM ONLY fs_ucastnici 
		UNION 
		SELECT userdb.email, userdb.name, (userdb.username)::text AS
username, 'userdb' FROM ONLY userdb 
		UNION 
		SELECT emaily.email, emaily.jmeno, ''::text, 'emaily' 
		FROM ONLY emaily WHERE (emaily.akce = 'add')
	) vsichni 
	WHERE (vsichni.email ~ '@'::text) 
	ORDER BY lower(vsichni.email);

When this schema exists, try:

SELECT * FROM infomail;

On my system, it fails with "ERROR:  replace_vars_with_subplan_refs:
variable not in subplan target list".



bye,
Michal Schwarz

Responses

pgsql-bugs by date

Next:From: Michal SchwarzDate: 2002-04-16 15:31:03
Subject: Case sensitivity in psql/pg_dump
Previous:From: Jochem van DietenDate: 2002-04-16 10:19:12
Subject: Re: Alter/update large tables - VERRRY annoying behaviour!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group