Re: [GENERAL] redirecting query statement and output to a marked up file, using psql
On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: Wim Bertels wrote: --user2 SET SESSION AUTHORIZATION user2; \pset format latex \echo ECHO queries \o report/test_user2.tex \i structure/test_user2.sql This doenst seem to work, as the ECHO queries output isnt written to the file (test_user2.tex) Actions are performed as they are encountered so put the \echo *after* the \o, like this: SET SESSION AUTHORIZATION user2; \pset format latex \o report/test_user2.tex \echo ECHO queries \i structure/test_user2.sql Hallo Bosco, i tried changing that, but it doesnt seem to work (the echo only affects the psql cmdl, but is not written to /o file) i am using a buil script, eg psql -f init/test_build_psql.sql dev or in psql \i init/test_build_psql.sql with the contents of test_build_psql.sql being: \set client_min_messages warning \set log_error_verbosity terse SET SESSION AUTHORIZATION user1; \i init/test_create.sql \i init/test_insert.sql SET SESSION AUTHORIZATION userX; \i init/test_grant.sql \i functions/Trigger_functions.sql SET SEARCH_PATH TO s1, s2, s3, s4; --functions report \pset format html \o report/functions_report.html \df --test student \o report/test_student.html \set ECHO queries \i init/test_student_try_out.sql --undo some settings \pset format aligned \set ECHO \o \set client_min_messages notice \set log_error_verbosity verbose mvg, Wim HTH, Bosco. -- 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] redirecting query statement and output to a marked up file, using psql
Wim Bertels wrote: On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: Wim Bertels wrote: --user2 SET SESSION AUTHORIZATION user2; \pset format latex \echo ECHO queries \o report/test_user2.tex \i structure/test_user2.sql This doenst seem to work, as the ECHO queries output isnt written to the file (test_user2.tex) Actions are performed as they are encountered so put the \echo *after* the \o, like this: SET SESSION AUTHORIZATION user2; \pset format latex \o report/test_user2.tex \echo ECHO queries \i structure/test_user2.sql Hallo Bosco, i tried changing that, but it doesnt seem to work (the echo only affects the psql cmdl, but is not written to /o file) Apologies, Wim. I meant to also indicate that you need to use the \qecho command. It echoes to the query output stream whereas \echo echoes to the stdout. So you would use: \qecho ECHO queries instead of the \echo above. Hopefully I didn't forget anything else this time. :-) HTH Bosco. -- 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] redirecting query statement and output to a marked up file, using psql
On Fri, 2011-02-04 at 03:23 -0800, Bosco Rama wrote: Wim Bertels wrote: On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: Wim Bertels wrote: --user2 SET SESSION AUTHORIZATION user2; \pset format latex \echo ECHO queries \o report/test_user2.tex \i structure/test_user2.sql This doenst seem to work, as the ECHO queries output isnt written to the file (test_user2.tex) Actions are performed as they are encountered so put the \echo *after* the \o, like this: SET SESSION AUTHORIZATION user2; \pset format latex \o report/test_user2.tex \echo ECHO queries \i structure/test_user2.sql Hallo Bosco, i tried changing that, but it doesnt seem to work (the echo only affects the psql cmdl, but is not written to /o file) Apologies, Wim. I meant to also indicate that you need to use the \qecho command. It echoes to the query output stream whereas \echo echoes to the stdout. So you would use: \qecho ECHO queries Hi Bosco, \qecho doenst interpret parameters it just echo text, in this case 'ECHO queries' mvg, Wim instead of the \echo above. Hopefully I didn't forget anything else this time. :-) HTH Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Setting configuration parameter to role and propagating it to users
Hi, I have following issue: I have several users with one role (and may have new users with the same role in the future so the role creation is justified). So I created: ALTER ROLE MY_ROLE SET search_path='my_schema'; But after doing it my_user (either existing or newly created) still cannot see my_schema (actually search_path is reset to $user,public). No what I can do to make this search_path configuration parameter to affect all users with this role? Or I should set it by hand to each user? Am I missing something ? Why I can set configuration parameter for role and not have users affected? I am using PostgreSQL 9.0.1. Please advise. Thanks regards, Joanna -- 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] Issues with generate_series using integer boundaries
On 3 February 2011 13:58, Thom Brown t...@linux.com wrote: On 3 February 2011 13:32, Thom Brown t...@linux.com wrote: Actually, further testing indicates this causes other problems: postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x); x --- 1 (1 row) Should return no rows. postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x); x 1 4 7 10 (4 rows) Should return 3 rows. Still messy code, but the attached patch does the job now: postgres=# SELECT x FROM generate_series(2147483643::int4,2147483647::int4) AS a(x); x 2147483643 2147483644 2147483645 2147483646 2147483647 (5 rows) postgres=# SELECT x FROM generate_series(2147483642::int4,2147483647::int4, 2) AS a(x); x 2147483642 2147483644 2147483646 (3 rows) postgres=# SELECT x FROM generate_series(2147483643::int4,2147483647::int4, 6) AS a(x); x 2147483643 (1 row) postgres=# SELECT x FROM generate_series((-2147483643)::int4, (-2147483648)::int4, -1) AS a(x); x - -2147483643 -2147483644 -2147483645 -2147483646 -2147483647 -2147483648 (6 rows) postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x); x --- (0 rows) postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x); x --- 1 4 7 (3 rows) Copying to -hackers. The issue is that generate_series will not return if the series hits either the upper or lower boundary during increment, or goes beyond it. The attached patch fixes this behaviour, but should probably be done a better way. The first 3 examples above will not return. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 generate_series_fix.v3.patch Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore: implied data-only restore\n
Hi list, i did a dump (one table) pg_dump -t tempo.lucas_p1000 -Fc -o -h 134.110.37.20 -p 5432 -U andi -W gis /disk2/samba/exportdb/postgres/lucas_p1000_test.out and when a want to restore the table pg_restore -d gis -t tempo.lucas_p1000 -Fc -v -h 134.110.37.20 -p 5432 -U andi -W /disk2/samba/exportdb/postgres/lucas_p1000_test.out pg_restore shows this error: pg_restore: implied data-only restore\n what is wrong? thanks for helpAndreas -- Dipl. Geoökologe Andreas Laggner Institut für Agrarrelevante Klimaforschung (AK) des vTI Arbeitsgruppe Emissionsinventare Johann Heinrich von Thünen-Institut (vTI), Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei Institute of Agricultural Climate Research (AK) of the vTI Johann Heinrich von Thünen-Institute (vTI), Federal Research Institute for Rural Areas, Forestry and Fisheries Bundesallee 50 D-38116 Braunschweig Tel.: (+49) (0)531 596 2636 Fax : (+49) (0)531 596 2645 E-mail: andreas.lagg...@vti.bund.de Homepage: http://www.vti.bund.de -- 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] pg_restore: implied data-only restore\n
On Friday, February 04, 2011 4:35:22 am Andreas Laggner wrote: Hi list, i did a dump (one table) pg_dump -t tempo.lucas_p1000 -Fc -o -h 134.110.37.20 -p 5432 -U andi -W gis /disk2/samba/exportdb/postgres/lucas_p1000_test.out and when a want to restore the table pg_restore -d gis -t tempo.lucas_p1000 -Fc -v -h 134.110.37.20 -p 5432 -U andi -W /disk2/samba/exportdb/postgres/lucas_p1000_test.out pg_restore shows this error: pg_restore: implied data-only restore\n what is wrong? Did it restore the data? According to the source pg_backup_archiver.c: Work out if we have an implied data-only restore. This can happen if the dump was data only or if the user has used a toc list to exclude all of the schema data. All we do is look for schema entries - if none are found then we set the dataOnly flag. Are you sure you are picking up the right lucas_p100_test.out file? Maybe there is a data only version lying around. One way to test is have pg_restore restore to a file instead of database: pg_restore -f tempo.sql -t tempo.lucas_p1000 /disk2/samba/exportdb/postgres/lucas_p1000_test.out thanks for helpAndreas -- Adrian Klaver adrian.kla...@gmail.com -- 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 to avoid repeating expensive computation in select
One last question. Are there any pitfalls if I roll my own ability to check for duplicate calls? Since I am using my own defined data type, and my own function, I could do this by: 1. in my data type X, adding fields for: a table oid, a row oid, a copy of a reference to the last 2nd argument, and a copy of the last result (in my case a double) 2. in my function (which takes two X args), if the new table/row/ref-2nd-arg match the last data (saved in the first arg), then return the last result, otherwise compute the new result and save this info to use on the next call This should enable only a single computation of the data for any given row as long as the same args are used as parameters. But, is this safe, or have any pitfalls I would need to look out for? Thanks! Bob --- On Thu, 2/3/11, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] how to avoid repeating expensive computation in select To: Bob Price rjp_em...@yahoo.com Cc: Bill Moran wmo...@potentialtech.com, pgsql-general@postgresql.org Date: Thursday, February 3, 2011, 5:33 PM Bob Price rjp_em...@yahoo.com writes: If I set the COST of expensivefunc high, and label it IMMUTABLE, will the query executor note that the two invocations to expensivefunc have the same inputs so it can only call it once and re-use the result the second time? No. There is a myth prevalent among certain wishful thinkers that IMMUTABLE does something like that, but it doesn't. IMMUTABLE only licenses the planner to fold a call *with constant arguments* into a constant result, by executing the function once before the query actually starts. Textually distinct calls of a function are not folded together in any case. regards, tom lane -- 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] redirecting query statement and output to a marked up file, using psql
Wim Bertels wrote: \qecho doenst interpret parameters it just echo text, in this case 'ECHO queries' Seems like you had two problems and I didn't see any reference to the second one initially. The first was the output of \echo going to the wrong place which is fixed by using \qecho. The second problem is that you are looking to have the variable 'ECHO' replaced in the \qecho command with its current value. This is done using the variable substitution syntax (i.e. the variable name within a pair of colons) like this: \set ECHO Hello \o testfile.txt \qecho :ECHO: world \q Will cause the testfile.txt file to have a line that reads: Hello world HTH Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Remove Role Membership
I've been searching the documentation and I've tried ALTER ROLE, REVOKE, etc etc etc can't seem to find anything that shows me how to remove membership roles from a particular user / role. I've granted a user name 'david' a member of 'finance' role but how do I remove the role membership from 'david'? Sorry for sounding completely dumb but I just can't seem to find this in the documentation. -- 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] set theory question
On 2011-02-03 18:41, Wappler, Robert wrote: On 2011-02-02, matty jones wrote: I am looking for a good book on the math and/or theory behind relational databases and associated topics.. I am looking some works on set theory, algebra, or any other books/papers on the mechanics that databases are built on. I found one book online, I can recommend Foundations of Databases (Abiteboul, Hull, Vianu) http://www.amazon.com/Foundations-Databases-Logical-Serge-Abiteboul/dp/0 201537710 Definately recommended (note this book is also called the 'Alice' book because of it's front cover picture). It enabled me to write http://arxiv.org/PS_cache/arxiv/pdf/0705/0705.3949v1.pdf regards, Yeb Havinga -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Additional Grants To SuperUser?
I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.
List of roles
Role name | Attributes | Member of
---+-+---
carlos | Superuser | {}
jmadeline | Create DB | {}
mwilshaw | Create DB | {}
postgres| Superuser | {}
: Create role
: Create DB
So from what I see above, 'carlos' is a superuser but do I need to
grant him 'CREATEROLE' 'CREATEDB' rights along with 'SUPERUSER' or
is 'SUPERUSER' by itself good enough?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] varchar (no 'N') vs. text
Let's say I have a database with two tables, a and b. Each has one column. 'a' has a column 't' of type text. 'b' has a column 'v' of type 'varchar' (no length specified). If I join the two tables, I see in the plan something that looks like this: Merge Cond: (a.t = (b.v)::text) I thought 'character varying' (aka varchar) sans length was an alias for text. Is it not? -- Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl.dll on windows with postgresql 9.0
I am upgrading a Windows install for a client of mine from 8.2.x to 9.0.3 and understand the pginstaller does not provide plperl for this version. ActivePerl 5.8 was already installed and after uninstalling 8.2 and installing 9.0.3, there is no plperl.dll in the lib folder. I thought this was due to the older version, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres to still not find the dll. How do I get the lib to install? I did a google and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plperl-td2264970.html Can someone help with how to get plperl module installed or confirm the version required? I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller. Is there a plperl package that I need to install similar to Unix? Thanks. -- Robert rob...@webtent.org -- 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] varchar (no 'N') vs. text
Jon Nelson jnelson+pg...@jamponi.net writes: I thought 'character varying' (aka varchar) sans length was an alias for text. Is it not? It has the same behavior, but it is a distinct type, so dummy coercions are needed. regards, tom lane -- 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] Additional Grants To SuperUser?
Not to be smart about it but you could just logon as carlos (or a different
superuser you create for this purpose) and issue Create Database xxx and
Create Role xxx statements and see whether they work. A superuser should
(imo) be able to do everything (including dropping) without any additional
permissions required so unless you see that carlos cannot I would say you
are good.
David J
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Carlos Mennens
Sent: Friday, February 04, 2011 1:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Additional Grants To SuperUser?
I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.
List of roles
Role name | Attributes | Member of
---+-+---
carlos | Superuser | {}
jmadeline | Create DB | {}
mwilshaw | Create DB | {}
postgres| Superuser | {}
: Create role
: Create DB
So from what I see above, 'carlos' is a superuser but do I need to grant him
'CREATEROLE' 'CREATEDB' rights along with 'SUPERUSER' or is 'SUPERUSER' by
itself good enough?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] Remove Role Membership
It appears from my GUI admin program that: REVOKE group-role FROM user-role; Should do the trick. From the documentation for REVOKE: http://www.postgresql.org/docs/9.0/static/sql-revoke.html REVOKE [ ADMIN OPTION FOR ] role_name [, ...] FROM role_name [, ...] [ CASCADE | RESTRICT ] David J -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Friday, February 04, 2011 12:52 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Remove Role Membership I've been searching the documentation and I've tried ALTER ROLE, REVOKE, etc etc etc can't seem to find anything that shows me how to remove membership roles from a particular user / role. I've granted a user name 'david' a member of 'finance' role but how do I remove the role membership from 'david'? Sorry for sounding completely dumb but I just can't seem to find this in the documentation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Additional Grants To SuperUser?
On Fri, Feb 4, 2011 at 2:18 PM, David Johnston pol...@yahoo.com wrote:
Not to be smart about it but you could just logon as carlos (or a different
superuser you create for this purpose) and issue Create Database xxx and
Create Role xxx statements and see whether they work. A superuser should
(imo) be able to do everything (including dropping) without any additional
permissions required so unless you see that carlos cannot I would say you
are good.
Yes but I'm trying to understand the difference because the default
'postgres' user that is auto-configured to have 'SUPERUSER',
'CREATEDB', 'CREATEROLE' grants. I'm trying to understand if those
are redundant grants or if there is a reason PostgreSQL developers
grant the 'postgres' user with SUPERUSER, CREATEDB, CREATEROLE.
Seems to me logically that if a someone is a superuser, then they
should be able to CREATEDB CREATEROLE, no? So why would the
'postgres' user need those additional attributes?
postgres=# \du
List of roles
Role name | Attributes | Member of
+-+---
cmennens | Superuser | {}
postgres | Superuser | {}
: Create role
: Create DB
--
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] varchar (no 'N') vs. text
On Fri, Feb 4, 2011 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jon Nelson jnelson+pg...@jamponi.net writes: I thought 'character varying' (aka varchar) sans length was an alias for text. Is it not? It has the same behavior, but it is a distinct type, so dummy coercions are needed. Are there any performance implications for this dummy coercion? Especially in joins. -- Jon -- 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] Additional Grants To SuperUser?
2011/2/4 Carlos Mennens carlos.menn...@gmail.com
On Fri, Feb 4, 2011 at 2:18 PM, David Johnston pol...@yahoo.com wrote:
Not to be smart about it but you could just logon as carlos (or a
different
superuser you create for this purpose) and issue Create Database xxx
and
Create Role xxx statements and see whether they work. A superuser
should
(imo) be able to do everything (including dropping) without any
additional
permissions required so unless you see that carlos cannot I would say you
are good.
Yes but I'm trying to understand the difference because the default
'postgres' user that is auto-configured to have 'SUPERUSER',
'CREATEDB', 'CREATEROLE' grants. I'm trying to understand if those
are redundant grants or if there is a reason PostgreSQL developers
grant the 'postgres' user with SUPERUSER, CREATEDB, CREATEROLE.
Seems to me logically that if a someone is a superuser, then they
should be able to CREATEDB CREATEROLE, no? So why would the
'postgres' user need those additional attributes?
These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes.
By performing ALTER ROLE postgres NOSUPERUSER it is possible to
turn role with a superuser status into a role that just can create databases
and manage roles (admin, but without superuser privileges).
postgres=# \du
List of roles
Role name | Attributes | Member of
+-+---
cmennens | Superuser | {}
postgres | Superuser | {}
: Create role
: Create DB
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
[GENERAL] How to extract a value from a record using attnum or attname?
PL/pgSQL seems tantalizingly close to being useful for developing a generalized trigger function for notifying the client of changes. I don't know whether I'm missing something or whether we're missing a potentially useful feature here. Does anyone see how to fill in where the commented question is, or do I need to write this function in C? Alternatively, I guess, I could write a C-based quote_literal(record, int2) and/or quote_literal(record, name) function to use there. create or replace function tcn_notify() returns trigger language plpgsql as $tcn_notify$ declare keycols int2vector; keycolname text; channel text; payload text; begin select indkey from pg_catalog.pg_index where indrelid = tg_relid and indisprimary into keycols; if not found then raise exception 'no primary key found for table %.%', quote_ident(tg_table_schema), quote_ident(tg_table_name); end if; channel := 'tcn' || pg_backend_pid()::text; payload := quote_ident(tg_table_name) || ',' || substring(tg_op, 1, 1); for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop select quote_ident(attname) from pg_catalog.pg_attribute where attrelid = tg_relid and attnum = keycols[i]::oid into keycolname; payload := payload || ',' || keycolname || '='; -- How do I append the quote_literal(value) ? end loop; perform pg_notify(channel, payload); return null; -- ignored because this is an AFTER trigger end; $tcn_notify$; It would surprise me if nobody else has wanted to do something like this. The only reason we hadn't hit it yet is that we'd been striving for portable code and had been doing such things in a Java tier outside the database. -Kevin -- 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 to extract a value from a record using attnum or attname?
Kevin Grittner wrote on 04.02.2011 23:27: PL/pgSQL seems tantalizingly close to being useful for developing a generalized trigger function for notifying the client of changes. I don't know whether I'm missing something or whether we're missing a potentially useful feature here. Does anyone see how to fill in where the commented question is, or do I need to write this function in C? Alternatively, I guess, I could write a C-based quote_literal(record, int2) and/or quote_literal(record, name) function to use there. create or replace function tcn_notify() returns trigger language plpgsql as $tcn_notify$ declare keycols int2vector; keycolname text; channel text; payload text; begin select indkey from pg_catalog.pg_index where indrelid = tg_relid and indisprimary into keycols; if not found then raise exception 'no primary key found for table %.%', quote_ident(tg_table_schema), quote_ident(tg_table_name); end if; channel := 'tcn' || pg_backend_pid()::text; payload := quote_ident(tg_table_name) || ',' || substring(tg_op, 1, 1); for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop select quote_ident(attname) from pg_catalog.pg_attribute where attrelid = tg_relid and attnum = keycols[i]::oid into keycolname; payload := payload || ',' || keycolname || '='; -- How do I append the quote_literal(value) ? end loop; perform pg_notify(channel, payload); return null; -- ignored because this is an AFTER trigger end; $tcn_notify$; It would surprise me if nobody else has wanted to do something like this. The only reason we hadn't hit it yet is that we'd been striving for portable code and had been doing such things in a Java tier outside the database. If you don't really need the key = value pairs, you can simply use: payload := payload || 'values: ' || ROW(old.*); this will append everything in one operation, but not in the col=value format Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CRUD functions, similar to SQL stored procedurs, for postgresql tables?
We use some SQLserver databases that have stored procedures for all C.R.U.D. functions so the same code is used no matter what language the developer is working in. The procedures are built by a master package that reads the table structures and creates the CRUD procedures. Then we modify the CRUD procedures for special issues on each table. This has worked well for us because we don't lose the logic if we have to change languages and we can return specific information on update failures to the calling object that helps debugging. Right now most of what we are doing is loading data from many old systems / many old languages (some not OOP) into a new Postgresql database. We are not sure yet what language or platform the replacement software will be written in so it is too early to set up MVC. I would like to do something similar with Postgresql functions. Are there any examples or best practices for this? Thanks, Margaret --- Examples: - Insert a single record into datalink CREATE PROC CHRM_datalink_Insert @dlID1 uniqueidentifier, @dlID2 uniqueidentifier, @dlLTID uniqueidentifier, @dlActive char(1), @dlEditBy uniqueidentifier = NULL, @dlEditDate datetime = NULL, @dlID uniqueidentifier = NULL AS INSERT datalink(dlID1, dlID2, dlLTID, dlActive, dlEditBy, dlEditDate, dlID) VALUES (@dlID1, @dlID2, @dlLTID, @dlActive, @dlEditBy, COALESCE (@dlEditDate, getdate()), newid()) GO -- -- Delete a single record from datalink CREATE PROC CHRM_datalink_Delete @dlID1 uniqueidentifier, @dlID2 uniqueidentifier, @dlLTID uniqueidentifier AS DELETE datalink WHERE dlID1 = @dlID1 AND dlID2 = @dlID2 AND dlLTID = @dlLTID GO - Update a single record in datalink CREATE PROC CHRM_datalink_Update @dlID1 uniqueidentifier, @dlID2 uniqueidentifier, @dlLTID uniqueidentifier, @dlActive char(1), @dlEditBy uniqueidentifier = NULL, @dlEditDate datetime, @dlID uniqueidentifier AS UPDATE datalink SET dlActive = @dlActive, dlEditBy = @dlEditBy, dlEditDate = COALESCE(@dlEditDate, getdate()), dlID = COALESCE(@dlID, newid()) WHERE dlID1 = @dlID1 AND dlID2 = @dlID2 AND dlLTID = @dlLTID GO This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain company proprietary, privileged or confidential information. If you are not the intended recipient(s), please contact the sender by reply e-mail, advise them of the error and destroy this message and its attachments as well as any copies. The review, use or distribution of this message or its content by anyone other than the intended recipient or senior management of the company is strictly prohibited. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Directing Partitioned Table Searches
In a partitioned table, is it possible to specify the partition for a query to search using a variable instead of a constant? EXAMPLE: Join another table to the partitioned one Table: clu (partitioned by state) ogc_fid bigint cluid char(16) state bpchar(2) constraint: state='mi' (or 'co', 'ks', etc. for each partition) Table: farms link bigint zone bpchar(2) farmid char(7) ... This selection will result in partitions being searched SELECT cluid, farmid FROM clu JOIN farms ON ogc_fid=link This selection will result in only the 'mi' (Michigan) partition being searched SELECT cluid, farmid FROM clu JOIN farms ON ogc_fid=link WHERE state='mi' And this selection will result in ALL partitions being searched. But why? SELECT cluid, farmid FROM clu JOIN farms ON ogc_fid=link WHERE state=zone I'd like to be able to run some queries w/o the overhead of searching partitions unnecessarily. Can it be done? Regards, -- *Bill Thoen* GISnet - www.gisnet.com http://www.gisnet.com/ 1401 Walnut St., Suite C Boulder, CO 80302 303-786-9961 tel 303-443-4856 fax bth...@gisnet.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature: vacuum page before write
Hi. I propose new feature. Before flushing page of table to disk it may be scanned to reclaim deleted rows. And assigned as partially vacuumed or vacuumed. It may reduce bloat in frequently updated tables and make normal (auto)vacuum faster. Additional scan adds overhead to cpu (for update/delete), but seq scan will be faster and disk io usage will be lower. I don't know what is happen when a index entry points to reclaimed row. -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
