From: | Erwin Moller <erwin(at)darwine(dot)nl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Strange error while working with derived table |
Date: | 2007-11-12 16:27:38 |
Message-ID: | 47387EFA.5050506@darwine.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Working on postgres8.1 in PHP, I came in the situation I needed to
create a derived table.
Consider the following query:
SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark' AS zoekwoord) UNION (SELECT 'test' AS zoekwoord)) AS
tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');
The part with the UNION simple fills tempwordstable.
This query runs fine.
However, if I have only 1 in tempwordstable (so no UNION), like this:
SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark' AS zoekwoord) ) AS tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');
I get the error:
ERROR: failed to find conversion function from "unknown" to text
If I DO cast 'mark' to TEXT like:
SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid,
CV.shortdesc
FROM tblcategoryvalues AS CV,
( (SELECT 'mark'::TEXT AS zoekwoord) ) AS tempwordstable
WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%');
all works fine.
I understand the ::TEXT part. Since I fill this 'table' on the fly, I
should tell WHAT I am using.
What I don't understand is why Postgres8.1 need the cast to TEXT for 1
entry in my derived table, and NOT when I use more (using UNION)??
Any clues much appriciated. :-)
Regards and TIA,
Erwin Moller
--
-------------------
Erwin Moller
Darwine BV
Groenendaal 25f
3011 SK Rotterdam
tel 010-2133996
-------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Mortell | 2007-11-12 16:41:11 | update record with two-column primary key |
Previous Message | Tom Lane | 2007-11-12 16:26:09 | Re: Regression in 8.3? |