PHP support

A preview of coming attractions in pgEdit 1.1: PHP support. PHP can be used in two different ways. First, it can be used to write plPHP stored procedures. In this case pgEdit looks for a $php$ dollar quote or the LANGUAGE parameter with 'plPHP' at the start of the function definition. If this is found, PHP syntax coloring is used for the body of the function.

PHP can also be used as a preprocessor to parameterize and dynamically generate SQL. pgEdit recognizes opening and closing PHP tags and automatically switches syntax coloring between PHP and SQL. The PHP output from the mixed syntax file is then passed to psql for processing.

The following is an example which illustrates these features. Notice how PHP heredoc strings can have different syntax coloring from PHP.

-- Example illustrating automatic switching between language syntax modes.


-- starting in sql mode
set search_path to public, pg_catalog;

-- table for logging inserts
create table insert_log (
    id serial PRIMARY KEY NOT NULL,
    tablename text NOT NULL,
    username text NOT NULL,
    stamp timestamp without time zone DEFAULT now() NOT NULL
);


-- create a trigger function to log inserts
CREATE FUNCTION tg_log_insert() RETURNS trigger AS $php$
    // The php dollar quote switches to php syntax coloring in the function body.
    $query = "INSERT into insert_log (tablename, username) VALUES ('"
        . $_TD['relname'] . "', current_user)";
    $rv = spi_exec_query($query);
    return;
$php$ LANGUAGE 'plphp';


<?php
// now in php
$log_table = 'insert_log';
$conn = pg_connect("dbname=test user=user1 password=user1");
if(!$conn) {
    echo "Unable to connect to test database\n";
    exit;
}

/* The '<<<' below signals the start of a php heredoc string. pgEdit looks at 
   the heredoc identifer to see if it is a syntax it can color. You can include
   php variables in the heredoc string in the same way as a double quoted string. */
$result = pg_query($conn, <<<sql
-- now in sql coloring
SELECT c.relname
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
    AND c.relname <> '$log_table'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;
sql
);


// get a list of tables
$rows = pg_fetch_all($result);

// write the create trigger statement for each table
foreach ($rows as $row) {
    $name = $row['relname'];
    echo <<<sql
create trigger tg_log_insert_$name after insert on $name
    for each row execute procedure tg_log_insert();\n\n
sql;
}

?>


-- back to sql coloring