Page 1 of 1

views in postgresql

Posted: Fri Jun 30, 2006 6:01 pm
by wtf
Greets,

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)
And I access it with this sql statement

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\'
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