PostgreSQL – Using PL/PythonU via phpPgAdmin

pgadminphppgadminplpythonpostgresql

I am used to PgAdmin III where I can simply create python functions like the following on a PostgreSQL database:

CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE;

And that works flawless:

plpythonu in pgadmin3

Now, executing exactly the same query on the same database with the same user gives me an error in phpPgAdmin:

ERROR:  syntax error at or near "CREATE"
LINE 1: SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION py...
                                       ^

Here is a screenshot:

plpythonu in phppgadmin

Now I'm wondering:

  1. How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin?
  2. Is it even possible to use PL/PythonU through phpPgAdmin?
  3. Why is it wrapping my query in a SELECT COUNT(*) FROM (...) AS sub query?

Like this: 

SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE) AS sub

This puzzles me a little bit and I couldn't find any information on the internet. As far as I understand the language support is installed into the database, so why is this query working on one interface but not on the other?

The host is ArchLinux with the following package versions installed:

  • extra/python2 2.7.9-1
  • extra/postgresql 9.4.1-1
  • community/phppgadmin 5.1-3
  • community/pgadmin3 1.20.0-1

Any ideas?

Best Answer

The trick is to uncheck the paginate results checkbox before executing the query. This creates that SELECT COUNT(*) ... wrapper.

Without that checkbox selected, creating the plpythonu query is working as expected.

I created the bug report #449 on sourceforge to see if this is really the desired behaviour.

Related Question