views in postgresql
Posted: Fri Jun 30, 2006 6:01 pm
Greets,
I'm trying to convert my query into postgresql view and I'm not having much luck. Here's the query
My current view looks like this
Any help is greatyl appreciated
And I access it with this sql statement
However this does not return desired results due to missing condition in the join. I can't find any reference on how to solve this. Any help is greatly appreciated.
~wtf
I'm trying to convert my query into postgresql view and I'm not having much luck. Here's the query
Code: Select all
$query = 'SELECT
f.id,
f.top_pos,
f.left_pos,
f.height,
f.width,
f.type,
CASE WHEN f.required = \'t\' THEN \'true\' ELSE \'false\' END as required,
f.class_id_fk,
f.visible,
i.value,
i.field_id_fk,
i.pdf_user_id_fk
FROM
pdf_field f
LEFT JOIN
pdf_input i
ON
(i.field_id_fk = f.id AND i.pdf_user_id_fk = ?)
WHERE
f.pdf_id_fk = ?
AND
f.page_num = ?
AND
f.visible = \'t\'';My current view looks like this
Any help is greatyl appreciated
Code: Select all
CREATE OR REPLACE VIEW fields_view AS
SELECT
f.id,
f.top_pos,
f.left_pos,
f.height,
f.width,
f.type,
CASE WHEN f.required = 't' THEN 'true' ELSE 'false' END as required,
f.class_id_fk,
f.pdf_id_fk,
f.page_num,
f.visible,
i.value,
i.field_id_fk,
i.pdf_user_id_fk
FROM
pdf_field f
LEFT JOIN
pdf_input i
ON
(i.field_id_fk = f.id)Code: Select all
SELECT * FROM
field_view
WHERE
i.pdf_user_id_fk = ?
AND
f.pdf_id_fk = ?
AND
f.page_num = ?
AND
f.visible = \'t\'~wtf