Re: [GENERAL] How best to load modules?
Hi, Here is the best cludge so far. To load the module 'tablefunc' from the contrib/ directory, process the output of the 'pg_config' program with unix commands. The 'pg_config' program is often distributed in a package separate from postgresql. \set tablefunc `pg_config|grep SHAREDIR|sed s/SHAREDIR = \(.*\)/\1\/contrib\/tablefunc.sql/g` \i :tablefunc This isn't very robust, but at least it allows me to load and unload stuff from a single sql script on two different distros. Cheers! On 28.01.11, Steve White wrote: Hello, all! What are best practices regarding the loading of postgresql modules, say from the contrib/ directory; specifically, with regard to portability? I would like to distribute an SQL script which loads a module, and works with as little further fiddling as possible. known options = Within a session, or in a script, one can use \i explicit file path But within a script this has the weakness that the file path varies from one system distribution to another. One can start psql with psql ... -f explicit file path but that's a measure taken outside the script, to done either with session, or else be done by a further measure such as a shell script. Ideally, the location of the default modules directory (or installation directory) should be available within a session in some variable or from some function call. There are some pre-defined variables, listed in a session by show all; but I don't see anything like a directory path there. Maybe a built-in function returning this directory? Searched to no avail: http://www.postgresql.org/docs/8.2/interactive/functions.html There has been talk about a bigger solution on http://wiki.postgresql.org/wiki/Module_Manager but little seems to have happened there in some years. An environment variable $libdir, is mentioned http://www.postgresql.org/docs/8.2/static/runtime-config-client.html but this seems not to be present within a session. It seems to be expanded within the LANGUAGE C environment, for instance in tablefunc.sql - CREATE OR REPLACE FUNCTION crosstab2(text) RETURNS setof tablefunc_crosstab_2 AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; - Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-ScienceZi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-ScienceZi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] include source file from another language into function body?
Hi, Is there an accepted way to load code in another language into a PostgreSQL function body? This would be really nice in several ways, for example: to run a code checker outside of postgresql, and to make things easier for source code colorizers. I have in mind something like CREATE OR REPLACE FUNCTION myfunc( arg1 TEXT, arg2 INTEGER ) RETURNS VOID AS 'Conversion.py' LANGUAGE PLPYTHONU; But when I try the above, on running the function I get an error could not compile..., which isn't surpising... For C functions, there is a special form of CREATE FUNCTION ... AS that causes an object file to be read... (In the case of Python, one could achieve something like this effect by using the execfile() function. However as I understand it, this would happen at run time...) Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-ScienceZi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How best to load modules?
Hello, all! What are best practices regarding the loading of postgresql modules, say from the contrib/ directory; specifically, with regard to portability? I would like to distribute an SQL script which loads a module, and works with as little further fiddling as possible. known options = Within a session, or in a script, one can use \i explicit file path But within a script this has the weakness that the file path varies from one system distribution to another. One can start psql with psql ... -f explicit file path but that's a measure taken outside the script, to done either with session, or else be done by a further measure such as a shell script. Ideally, the location of the default modules directory (or installation directory) should be available within a session in some variable or from some function call. There are some pre-defined variables, listed in a session by show all; but I don't see anything like a directory path there. Maybe a built-in function returning this directory? Searched to no avail: http://www.postgresql.org/docs/8.2/interactive/functions.html There has been talk about a bigger solution on http://wiki.postgresql.org/wiki/Module_Manager but little seems to have happened there in some years. An environment variable $libdir, is mentioned http://www.postgresql.org/docs/8.2/static/runtime-config-client.html but this seems not to be present within a session. It seems to be expanded within the LANGUAGE C environment, for instance in tablefunc.sql - CREATE OR REPLACE FUNCTION crosstab2(text) RETURNS setof tablefunc_crosstab_2 AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; - Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-ScienceZi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How best to load modules?
Hi Dimitri! PGXS is interesting, but a bigger solution than I was looking for: ideally, some simple commands for loading the module from my .sql script. pg_config seems to be in yet another package, postgresql84-devel. It is a shell utility for getting such information. This is again far removed from the postgresql session, and more bother for the user to install. Maybe I could run it from the script, regex it for the SHAREDIR key, and construct from that '$SHAREDIR/contrib'. A simple variable or function returning the library path would have solved my present problem. Perhaps we should make a feature request. A proper notion of a module (something like the Python import command) would be really nice, of course, and should already have been there a long time ago, and it seems to be the aim of the Module_Manager proposal. Cheers! On 28.01.11, Dimitri Fontaine wrote: Steve White swh...@aip.de writes: What are best practices regarding the loading of postgresql modules, say from the contrib/ directory; specifically, with regard to portability? I would like to distribute an SQL script which loads a module, and works with as little further fiddling as possible. See about PGXS. http://www.postgresql.org/docs/9.0/static/xfunc-c.html#XFUNC-C-PGXS known options = Within a session, or in a script, one can use \i explicit file path But within a script this has the weakness that the file path varies from one system distribution to another. One can start psql with psql ... -f explicit file path but that's a measure taken outside the script, to done either with session, or else be done by a further measure such as a shell script. Ideally, the location of the default modules directory (or installation directory) should be available within a session in some variable or from some function call. You can use pg_config to get this PATH, and in recent versions of PostgreSQL you can use $libdir as the module directory name. select name, setting from pg_settings where name ~ 'dynamic_library_path'; There has been talk about a bigger solution on http://wiki.postgresql.org/wiki/Module_Manager but little seems to have happened there in some years. It seemed stalled for a long time because the harder part of this development was to get an agreement among hackers about what to develop exactly. We've been slowly reaching that between developer meetings in 2009 and 2010, and the result should hit the official source tree before the next developer meeting in 2011 :) https://commitfest.postgresql.org/action/patch_view?id=471 https://commitfest.postgresql.org/action/patch_view?id=472 If you want to see more details about the expected-to-be-commited-soon development work, have a look there: http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html If you have enough time and interest into the feature, you can even clone the git repository where the development occurs (branches named extension and upgrade) and try it for yourself, then maybe send a mail about your findings (we call that a review): http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary http://wiki.postgresql.org/wiki/Reviewing_a_Patch Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-ScienceZi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] access computed field of RECORD variable
Alvaro, I followed your advice, but using PL/Python. I succeeded, but only with great difficulty. To close this off, I'll write these down, together with the work-arounds. Some of this info would be of use if it were in the documentation. Problems 1) (documentation) The doc says Python functions can't be called directly from Python! But more accurately, PostgreSQL functions written in Python can't be called from Python. 2) Tragically, the plpy.execute() function flattens composite fields to string. In my case a work-around was simple. For others, this will be a killer. 3) PL/Python doesn't permit functions that return RECORD or SETOF RECORD. But I want to return a generic table... (or do I?) Work-arounds 1) Python is one of those languages that allow functions to be defined within the body of another function. So within the body of a PostgreSQL function definition, you can build a little Python environment containing everything you would expect: functions, classes, etc, and proceed to do serious programming. One can basically treat the body of a PL/Python function as an individual Python script, with the 'plpy' module already included. 2) I wrote about this in a different posting, that nobody has yet answered. In my case, I was able to parse the string without too much trouble. But this is really a crying shame. 3a) So PL/Python doesn't return SETOF RECORD. The trick I implemented was to call it from a PL/pgSQL which created a temp table, which was filled by the Python. This function in turn could return the set of generic records using RETURN NEXT. It would be nice PL/Python could return RECORD--I don't know why it shouldn't. 3b) In my case though, It didn't matter. I ended up needing functions that returned tables of a set of known types anyway. But the set is large, and unfortunately, there is no way to pass the return type of a function as a parameter. It has to be known at create time. The solution was, to write a function that did a CREATE FUNCTION to create a function of the desired types. This way for each known table, only one SELECT needs to be done, to create the needed function for that table. Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | e-Science / AstroGrid-D Zi. 35 Bg. 20 | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Python flattens composite types to string?
Hi, I recently wrote PL/Python code that worked on fields of composite types. The plpy.execute() command on a SELECT returns a list of nice dictionaries keyed on field names, containing the fields. For numeric types, the type of the dictionary values are as expected. To my chagrin however, if a field contains a composite type, it is flattened to a string. I would have expected a composite type field to be returned as a dictionary of values of the proper types, keyed on the names of the elements of the composite type. I was able to work around this problem in an ugly way, but I can imaging cases where this would render plpy unsuitable. I see nothing in the documentation about this http://www.postgresql.org/docs/8.4/static/plpython.html It only talks about passing composite types into and out of functions. Is this a bug? This is psql v. 8.1.18 on one machine, and 8.4.1 on another. Am I somehow doing it wrong? -- See attached test Cheers! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | e-Science / AstroGrid-D Zi. 35 Bg. 20 | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- vim:set filetype=pgsql: /** Illustrates PL/Py flattening of composite types * Log in as user 'postgres' in directory containing this file, start psql, then create language plpythonu;-- if haven't done already \i pycomptype.sql select pycomptypes.color_read( 1 ); * To get rid of the tables drop schema pycomptypes cascade; */ CREATE SCHEMA pycomptypes AUTHORIZATION postgres; SET search_path TO pycomptypes; CREATE TYPE value_sigma AS ( value DOUBLE PRECISION, sigma DOUBLE PRECISION ); CREATE TABLE colors ( colors_id INTEGER PRIMARY KEY, red value_sigma, green value_sigma, bluevalue_sigma ); INSERT INTO colors VALUES ( 1, (1.21, 0.05), (1.45, 0.06), (1.83, 0.07) ); INSERT INTO colors VALUES ( 2, (0.94, 0.05), (0.38, 0.03), (1.81, 0.07) ); INSERT INTO colors VALUES ( 3, (0.56, 0.02), (0.74, 0.05), (1.90, 0.08) ); CREATE OR REPLACE FUNCTION color_read( color_id INTEGER ) RETURNS VOID AS $$ cmd = 'SELECT red,green,blue FROM colors WHERE colors_id=' + str( color_id ) for t in plpy.execute( cmd ): plpy.notice( 'type of color item: %s' % ( type( t['red'] ) ) ) $$ LANGUAGE PLPYTHONU; GRANT SELECT ON colors TO PUBLIC; GRANT USAGE ON SCHEMA pycomptypes TO PUBLIC; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] access computed field of RECORD variable
Hi, I ran into a roadblock at the very bottom of a fairly large database design implementation. It will be at least messy to fix, unless there is a neat solution. The roadblock is: There is a record, which may be from any of a set of similar databases. Field names for this record are computed as strings. Using these strings, I need to access fields of the record. But I can't find the syntax for it. Now, if a record variable is a known row type, I can get a field of a computed name. For a generic RECORD I can get a field by putting an explicit name the code, (rec).FieldName1 But can one get the value of a computed field from a generic RECORD? With rec RECORD; field TEXT; The following all fail: res := rec.field; -- ERROR: record rec has no field field res := (rec.field); -- ERROR: record rec has no field field res := (rec).field; -- ERROR: syntax error at or near $2 res := rec.(field); -- ERROR: syntax error at or near ( res := rec.FieldName1; -- gets field, but isn't what is needed I also tried numerous other things that were doomed to fail. Find an example file attached. Just \i it and play with the possibilites. Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | e-Science / AstroGrid-D Zi. 35 Bg. 20 | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- vim:set filetype=pgsql: SET client_min_messages TO NOTICE; CREATE TABLE example1 ( FieldName1 INTEGER PRIMARY KEY ); INSERT INTO example1 (FieldName1) VALUES ( 1 ); -- - CREATE FUNCTION get_field_from_cursor( field TEXT, curs REFCURSOR ) RETURNS INTEGER AS $body$ DECLARE rec RECORD; res INTEGER; BEGIN RAISE NOTICE 'Getting field %', field; FETCH curs INTO rec; --res := rec.field; -- ERROR: record rec has no field field --res := (rec.field); -- ERROR: record rec has no field field --res := (rec).field; -- ERROR: syntax error at or near $2 --res := rec.(field); -- ERROR: syntax error at or near ( --res := rec.FieldName1; -- gets field, but isn't what is needed --SELECT INTO res field FROM rec; -- ERROR: syntax error at or near $2 return res; END; $body$ LANGUAGE PLPGSQL; -- - CREATE FUNCTION test() RETURNS VOID AS $body$ DECLARE cursREFCURSOR; result INTEGER; BEGIN OPEN curs FOR SELECT * FROM example1; result := get_field_from_cursor( 'FieldName1', curs ); RAISE NOTICE 'Got field %', result; END; $body$ LANGUAGE PLPGSQL; -- - GRANT SELECT ON example1 TO PUBLIC; SELECT test(); -- - DROP TABLE example1; DROP FUNCTION test(); DROP FUNCTION get_field_from_cursor( TEXT, REFCURSOR); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] access computed field of RECORD variable
Merlin, There is a set of databases whose columns are differently named and have different meanings, but which are processed similarly (the data is all floats, and some dot products need to be made). The processing to be done can all described by other tables which provide coefficents based on the names of the fields in those columns. Now, I have gone so far as to pass explicit query strings around, to make a separate query for each field. But the programming is awful compared to the cursors approach, besides probabaly being quite inefficient. One can also do an EXECUTE INTO but it has the same problem that the type of the record is unknown (Well, it might be known at run time... If I knew the type of the record at run time, could use that somehow?). Another step would be to scrap the whole idea of named columns for these databases, but that would be a pity, because it bought us so much in other ways (e.g. tight association of the interpretation of the columns with their data). Besides, I already wrote code for all that! Any ideas? On 5.01.10, Merlin Moncure wrote: On Tue, Jan 5, 2010 at 8:53 AM, Steve White swh...@aip.de wrote: Hi, I ran into a roadblock at the very bottom of a fairly large database design implementation. It will be at least messy to fix, unless there is a neat solution. The roadblock is: There is a record, which may be from any of a set of similar databases. Field names for this record are computed as strings. Using these strings, I need to access fields of the record. But I can't find the syntax for it. Now, if a record variable is a known row type, I can get a field of a computed name. For a generic RECORD I can get a field by putting an explicit name the code, (rec).FieldName1 But can one get the value of a computed field from a generic RECORD? why are you using generic records then? :-) Maybe there is an elegant solution to your issue with a bit more context. merlin -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | e-Science / AstroGrid-D Zi. 35 Bg. 20 | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
