[TinyOWS-users] tinyows with views

[Marc-André Trottier]

so i harcoded my primary key in ows_storage.c
function ows_storage_fill_pkey() look like this :

static void ows_storage_fill_pkey(ows * o, ows_layer * l)
{
    buffer *sql;
    PGresult *res;

    assert(o);
    assert(l);
    assert(l->storage);

    sql = buffer_init();

    buffer_add_str(sql, "SELECT c.column_name FROM
information_schema.constraint_column_usage c, pg_namespace n ");
    buffer_add_str(sql, "WHERE n.nspname = '");
    buffer_copy(sql, l->storage->schema);
    buffer_add_str(sql, "' AND c.table_name = '");
    buffer_copy(sql, l->storage->table);
    buffer_add_str(sql, "' AND c.constraint_name = (");

    buffer_add_str(sql, "SELECT c.conname FROM pg_class r, pg_constraint c,
pg_namespace n ");
    buffer_add_str(sql, "WHERE r.oid = c.conrelid AND relname = '");
    buffer_copy(sql, l->storage->table);
    buffer_add_str(sql, "' AND r.relnamespace = n.oid AND n.nspname = '");
    buffer_copy(sql, l->storage->schema);
    buffer_add_str(sql, "' AND c.contype = 'p')");

    res = ows_psql_exec(o, sql->buf);
    /*----------------------ENLEVER------------------*/
   /* if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        PQclear(res);
        buffer_free(sql);
        ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_*
tables.", "pkey column");
        return;
    }*/

    /* Layer could have no Pkey indeed... (An SQL view for example) */
    /* if (PQntuples(res) == 1) {*/
        l->storage->pkey = buffer_init();
        /*----------ENLEVER-------------*/
       /* buffer_add_str(l->storage->pkey, PQgetvalue(res, 0, 0));*/
        buffer_add_str(l->storage->pkey, "ogc_fid");
        buffer_empty(sql);
        PQclear(res);

        /* Retrieve the Pkey column number */
        buffer_add_str(sql, "SELECT a.attnum FROM pg_class c, pg_attribute
a, pg_type t, pg_namespace n");
        buffer_add_str(sql, " WHERE a.attnum > 0 AND a.attrelid = c.oid AND
a.atttypid = t.oid AND n.nspname='");
        buffer_copy(sql, l->storage->schema);
        buffer_add_str(sql, "' AND c.relname='");

        buffer_copy(sql, l->storage->table);
        buffer_add_str(sql, "' AND a.attname='ogc_fid'");

        /* buffer_copy(sql, l->storage->pkey);
        buffer_add_str(sql, "'");*/
        res = ows_psql_exec(o, sql->buf);
        if (PQresultStatus(res) != PGRES_TUPLES_OK) {
            PQclear(res);
            buffer_free(sql);
            ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to find pkey
column number.", "pkey_column number");
            return;
        }

        /* -1 because column number start at 1 */
        l->storage->pkey_column_number = atoi(PQgetvalue(res, 0, 0)) - 1;
        buffer_empty(sql);
        PQclear(res);

        /* Now try to find a sequence related to this Pkey
        buffer_add_str(sql, "SELECT pg_get_serial_sequence('");
        buffer_copy(sql, l->storage->schema);
        buffer_add_str(sql, ".\"");
        buffer_copy(sql, l->storage->table);
        buffer_add_str(sql, "\"', '");
        buffer_copy(sql, l->storage->pkey);
        buffer_add_str(sql, "');");
        */

        /*NEW function in PG*/
        buffer_add_str(sql, "SELECT get_sequence_view('");
        buffer_copy(sql, l->storage->table);
        buffer_add_str(sql, "');");

        res = ows_psql_exec(o, sql->buf);
        if (PQresultStatus(res) != PGRES_TUPLES_OK) {
            PQclear(res);
            buffer_free(sql);
            ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
                  "Unable to use pg_get_serial_sequence.", "pkey_sequence
retrieve");
            return;
        }

        /* Even if no sequence found, this function return an empty row
         * so we must check that result string returned > 0 char
         */
        if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) >
0) {
            l->storage->pkey_sequence = buffer_init();
            buffer_add_str(l->storage->pkey_sequence, PQgetvalue(res, 0,
0));
        }
/*    }*/

    PQclear(res);
    buffer_free(sql);
}


and get_sequence_view() looks like :

CREATE OR REPLACE FUNCTION get_sequence_view(name_table character varying)
  RETURNS text AS
$BODY$

DECLARE

    viewn varchar;
    resultat varchar := '';
    nom_seq varchar;
    current_seq integer;

BEGIN

    viewn := name_table;

    SELECT table_name FROM information_schema.view_table_usage where
view_name = viewn into resultat;

    /* if resultat <> "" so view else it's a table */

    if (resultat <> '') then
        SELECT pg_get_serial_sequence(resultat,'ogc_fid')::text into
nom_seq;
        --SELECT currval(nom_seq) into current_seq;
    ELSE
        SELECT pg_get_serial_sequence(viewn,'ogc_fid')::text into nom_seq;
        --SELECT currval(nom_seq) into current_seq;
    END IF;

    RETURN nom_seq;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


very personalized but it works when
1- table got primary key : ogc_fid
2- view content ogc_fid
3- view got just one table
4- view with rules on INSERT, DELETE, UPDATE
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.maptools.org/pipermail/tinyows-users/attachments/20110708/b060665b/attachment.htm