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
-------------------
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
