[GENERAL] cannot use column references in default expression?
Could anyone please help me to get rid of following error? I want to set the 'ishuman' column based on the value of 'ID' column but it is not allowing me to do so. Any alternatives? techdb= CREATE TABLE Users ( ID INTEGER, isHumanBOOLEAN NOT NULL DEFAULT (ID IS NULL) CHECK (isHuman = ID IS NULL), Name VARCHAR NOT NULL); ERROR: cannot use column references in default expression techdb= Thanks, Jignesh
Re: [GENERAL] cannot use column references in default expression?
Trigger should be the last solution. This used to be working but I think with latest postgresql upgrade, this stopped working. Might be someone around here knows whats going on here. Thanks, Jignesh On Fri, May 28, 2010 at 11:00 PM, Craig Ringer cr...@postnewspapers.com.auwrote: On 29/05/2010 1:20 AM, Jignesh Shah wrote: Could anyone please help me to get rid of following error? I want to set the 'ishuman' column based on the value of 'ID' column but it is not allowing me to do so. Any alternatives? Use a BEFORE trigger to set it. -- Craig Ringer
Re: [GENERAL] Issue in Improving the performance using prepared plan
:) I realized that. Thanks. On Thu, Apr 22, 2010 at 6:53 PM, Greg Sabino Mullane g...@turnstep.comwrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I have written following trigger and trying to improve the performance by using prepared query everytime. I have used spi_prepare to prepare the query and $_SHARED global hash to persist the prepared plan but it doesn't seem to work. Though $query will be same always in following trigger, it prepares query everytime and never uses prepared plan. Could anyone tell me what's wrong going on? Works fine for me. Note that your elog outputs are switched - you are claiming the already prepared plan for the first time (if exists) and claiming the first prepare when in fact it is reusing (else). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004220922 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkvQTasACgkQvJuQZxSWSsiH1wCgwiuBRmjmGZ0WWKKD/6BwovhR M7IAoME88RAuNAd0P1tH4ug/I8FFJ8Bj =CG70 -END PGP SIGNATURE- -- 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] Syntax error in trigger
Thanks Anreas but I think that's not a issue. The issue is something to do
with $1. I don't know how to get rid of it.
techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table
WHERE id = 5;
INSERT 0 1
techdb=# select * from log_table;
id | txid | txtime
++
5 | 196552 | 2010-04-22 09:30:10.509326
(1 row)
techdb=#
Could anyone please help me out here.
Thanks,
Jignesh
On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Jignesh Shah jignesh.shah1...@gmail.com wrote:
Hi All,
I have written below trigger and applied on the table but it is giving
syntax
error when it gets invoked. Could you please help me what is wrong? I
have
given complete details here:
my trigger function:
CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
RETURNS trigger AS
$BODY$
my $id= $_TD-{new}{'id'};
my $query =
INSERT INTO log_table(id)
SELECT $1
EXCEPT SELECT id -- throwing error for here: syntax error at or
near
Select from which table? I think, there is the table-name missing,
right?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] Syntax error in trigger
I got it resolved. I just need to put below statement into single quote
instead of double quote. :)
my $query ='INSERT INTO log_table(id) SELECT $1 EXCEPT SELECT id FROM
log_table WHERE id = $1;';
Thanks,
Jignesh
On Thu, Apr 22, 2010 at 2:02 PM, Jignesh Shah jignesh.shah1...@gmail.comwrote:
Thanks Anreas but I think that's not a issue. The issue is something to do
with $1. I don't know how to get rid of it.
techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table
WHERE id = 5;
INSERT 0 1
techdb=# select * from log_table;
id | txid | txtime
++
5 | 196552 | 2010-04-22 09:30:10.509326
(1 row)
techdb=#
Could anyone please help me out here.
Thanks,
Jignesh
On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Jignesh Shah jignesh.shah1...@gmail.com wrote:
Hi All,
I have written below trigger and applied on the table but it is giving
syntax
error when it gets invoked. Could you please help me what is wrong? I
have
given complete details here:
my trigger function:
CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
RETURNS trigger AS
$BODY$
my $id= $_TD-{new}{'id'};
my $query =
INSERT INTO log_table(id)
SELECT $1
EXCEPT SELECT id -- throwing error for here: syntax error at or
near
Select from which table? I think, there is the table-name missing,
right?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Issue in Improving the performance using prepared plan
Hi,
I have written following trigger and trying to improve the performance by
using prepared query everytime. I have used spi_prepare to prepare the query
and $_SHARED global hash to persist the prepared plan but it doesn't seem to
work. Though $query will be same always in following trigger, it prepares
query everytime and never uses prepared plan.
Could anyone tell me what's wrong going on?
CREATE OR REPLACE FUNCTION techdb_table_trigger()
RETURNS trigger AS
$BODY$
our ($id, $query, $plan, $change_log_table);
$change_log_table = ChangeLogTable;
$id = $_TD-{new}{'id'};
$query = (ENDQUERY);
INSERT INTO $change_log_table(id)
SELECT \$1
EXCEPT SELECT id
FROM $change_log_table
WHERE id = \$1
AND txid = txid_current()
AND txtime = transaction_timestamp();
ENDQUERY
if (exists($_SHARED{$query})) {
$plan = $_SHARED{$query};
* elog(INFO, ## Preparing the query ###); -- Always
comes here. Don't know why?*
} else {
$plan = spi_prepare($query, 'INTEGER');
$_SHARED{$query} = $plan;
* elog(INFO, ###Using already prepared the
query##); -- Never comes here.*
}
spi_exec_prepared($plan, $id);
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks,
Jignesh
[GENERAL] Syntax error in trigger
Hi All,
I have written below trigger and applied on the table but it is giving
syntax error when it gets invoked. Could you please help me what is wrong? I
have given complete details here:
*my trigger function:*
**
CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
RETURNS trigger AS
$BODY$
my $id= $_TD-{new}{'id'};
my $query =
INSERT INTO log_table(id)
* SELECT $1
EXCEPT SELECT id -- throwing error for here: syntax error at or near
EXCEPT*
FROM loggingtable
WHERE id = $1;;
if (exists($_SHARED{$query})) {
$plan = $_SHARED{$query};
} else {
$plan = spi_prepare($query, 'INTEGER');
$_SHARED{$query} = $plan;
}
spi_exec_prepared($plan, '$id');
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
*Tables and trigger definitions:*
CREATE TABLE techdb_table( id integer NOT NULL, info varchar NOT NULL );
CREATE TABLE log_table( id integer NOT NULL, txid integer NOT NULL DEFAULT
txid_current(), txtime timestamp NOT NULL DEFAULT transaction_timestamp());
CREATE TRIGGER techdb_trigger
BEFORE INSERT ON techdb_table
FOR EACH ROW EXECUTE PROCEDURE techdb_logtable_trigger();
*Error message:*
Executing insert into techdb_table values(1, 'test'); command gives below
error.
ERROR: error from Perl function techdb_logtable_trigger: syntax error at
or near EXCEPT at line 15.
** Error **
ERROR: error from Perl function techdb_logtable_trigger: syntax error at
or near EXCEPT at line 15.
SQL state: XX000
Thanks,
Jignesh
Re: [GENERAL] Get the list of permissions on schema for current user
I don't think you can do it. You have to parse the string you got from
pg_namespace to get the current user's permissions.
On Thu, Apr 1, 2010 at 11:09 AM, dipti shah shahdipti1...@gmail.com wrote:
Hi,
I ran below command to list out all privileges of objects if mydb schema.
Actually, I want to know what are the permissions user1 has on mydb
schema. Could you please tell me how to do this?
mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
pc.relnamespace=pn.oid and pn.nspname='mydb';
relname |relacl
--+---
mylog |
{postgres=arwdDxt/postgres,=arwdDxt/postgres}
techtable |
{postgres=arwdDxt/postgres,=ar/postgres}
techtable_log |
hrtable |
{postgres=arwdDxt/postgres,=ar/postgres}
hrtable_log |
(5 rows)
mydb= select current_user;
current_user
--
user1
(1 row)
mydb=
Thanks, Dipti
[GENERAL] has_schema_privilege function
Hi, I have created below function. I am checking return value of
has_schema_privilege by using flag=f. I think this is not strong way to
make a check because if in future f becomes false my stored procedure
will work improper.
Could you tell me is there any other robust way to make sure that user1
doesn't have CREATE permissions on mydb schema?
CREATE OR REPLACE FUNCTION schema_perm_test()
RETURNS void AS
$BODY$
$rv = spi_exec_query(SELECT has_schema_privilege('user1', 'mydb',
'CREATE') AS flag;);
if(lc($rv-{rows}-[0]-{flag}) eq f) {
# Do tasks
}
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks.
[GENERAL] Not able to change the owner of function
Hi, could you tell me what could be the issue in below command. I could see that there is an option for changing OWNER of function but not sure why it is giving this error. techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; ERROR: unrecognized configuration parameter owner techdb=# Thanks in advance, Jack
[GENERAL] How to get the permissions assigned to user?
Hi, Is there any way to get the set of permissions list assigned to user? I want to know whether user has create table permissions on particular schema or not? Thanks in advance, Jack
Re: [GENERAL] [NOVICE] Not able to change the owner of function
Hey, I have read it and current user is 'postgres' and the new_user is also looks fine but still the same error. techdb=# select current_user; current_user -- postgres (1 row) techdb=# CREATE OR REPLACE FUNCTION test_create() RETURNS void AS $BODY$ $cmd = CREATE TABLE testtable(col varchar not null);; spi_exec_query(CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS '$cmd' LANGUAGE SQL;); spi_exec_query(SELECT myfunc();); $BODY$ LANGUAGE 'plperl'; CREATE FUNCTION techdb=# ALTER FUNCTION test_create() SET OWNER TO user1; ERROR: unrecognized configuration parameter owner techdb=# Thanks, Jack On Wed, Feb 24, 2010 at 11:51 AM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Jignesh Shah : Hi, could you tell me what could be the issue in below command. I could see that there is an option for changing OWNER of function but not sure why it is giving this error. techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; ERROR: unrecognized configuration parameter owner test=*# \h alter function Command: ALTER FUNCTION Description: change the definition of a function Syntax: ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) OWNER TO new_owner Read that and try: ALTER FUNCTION test_create() OWNER TO masanip; Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-novice mailing list (pgsql-nov...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Re: [GENERAL] Not able to change the owner of function
Thanks :) I am going crazy at times. On Wed, Feb 24, 2010 at 12:04 PM, Frank Heikens frankheik...@mac.comwrote: Skip the SET-keyword: ALTER FUNCTION test_create() OWNER TO masanip; Regards, Frank Op 24 feb 2010, om 07:14 heeft Jignesh Shah het volgende geschreven: Hi, could you tell me what could be the issue in below command. I could see that there is an option for changing OWNER of function but not sure why it is giving this error. techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; ERROR: unrecognized configuration parameter owner techdb=# Thanks in advance, Jack
[GENERAL] Questions regarding SET option.
Hello All, I have been writing a function with SECURITY DEFINER enabled. Basically, I am looking for ways to override the users SET option settings while executing my function to prevent the permissions breach. For example, to override SET search_path, I am setting search path in my function before executing anything. Could any one please tell me what could be other SET options that I should take care? Moreover, how to revert back those settings just before returning from my function? Thanks, Jack
Re: [GENERAL] Questions regarding SET option.
Thanks a ton Laurenz and Pavel for your responses but I really didn't follow you. I am not master in PostGreSQL yet. Could you please give me some example? Basically, I want to know how many such SET options I should reset before executing my function and at the end it should also be restored to original settings. It would be really helpful if you could elaborate your response. Thanks guys. Jack On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Jignesh Shah wrote: I have been writing a function with SECURITY DEFINER enabled. Basically, I am looking for ways to override the users SET option settings while executing my function to prevent the permissions breach. For example, to override SET search_path, I am setting search path in my function before executing anything. Could any one please tell me what could be other SET options that I should take care? Moreover, how to revert back those settings just before returning from my function? You can use the SET clause of CREATE FUNCTION which does exactly what you want. Yours, Laurenz Albe
Re: [GENERAL] Questions regarding SET option.
set work_mem to '1MB' set search_path = 'public'; Thanks for the example Pavel. I understood it. Are there any other SET options except above that I need to set to prevent security breach? Thanks, Jack On Mon, Feb 22, 2010 at 11:41 PM, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/2/22 Jignesh Shah jignesh.shah1...@gmail.com: Thanks a ton Laurenz and Pavel for your responses but I really didn't follow you. I am not master in PostGreSQL yet. Could you please give me some example? Basically, I want to know how many such SET options I should reset before executing my function and at the end it should also be restored to original settings. create or replace function foop() returns int as $$ select 10 $$ language sql set work_mem to '1MB' set search_path = 'public'; CREATE FUNCTION postgres=# regards Pavel Stehule It would be really helpful if you could elaborate your response. Thanks guys. Jack On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Jignesh Shah wrote: I have been writing a function with SECURITY DEFINER enabled. Basically, I am looking for ways to override the users SET option settings while executing my function to prevent the permissions breach. For example, to override SET search_path, I am setting search path in my function before executing anything. Could any one please tell me what could be other SET options that I should take care? Moreover, how to revert back those settings just before returning from my function? You can use the SET clause of CREATE FUNCTION which does exactly what you want. Yours, Laurenz Albe
[GENERAL] How to get the users name from users group?
Hello All, I have created role database-users and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE dbuser1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser1; CREATE ROLE dbuser2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser2; How to query database-users to list the dbuser1 and dbuser2? Thanks in advance, Jack
Re: [GENERAL] How to get the users name from users group?
Exactly the same! Thanks a ton. On Fri, Feb 19, 2010 at 3:58 PM, Magnus Hagander mag...@hagander.netwrote: 2010/2/19 Jignesh Shah jignesh.shah1...@gmail.com: Hello All, I have created role database-users and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE dbuser1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser1; CREATE ROLE dbuser2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser2; How to query database-users to list the dbuser1 and dbuser2? I think you're looking for: SELECT rolname from pg_authid a INNER JOIN pg_auth_members m ON m.member=a.oid WHERE m.roleid=(SELECT oid FROM pg_authid WHERE rolname='database-users') or something similar to that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
[GENERAL] Setting permissions to access schema and language
Hi, I know how to set the permissions for tables. Could anyone tell me how to restrict people accessing and creating schemas? Also, is it possible to restrict language permissions? Suppose I want only few users should use C language and for rest of users it should be missing. How can I restrict permissions on languages? Thanks in advanced, Jignesh
[GENERAL] What is statement ID of table?
Hi, Could any one please tell me what is statement ID of table? How to get it and in which scenarios it can be helpful? Any documentation or example about statement ID would also really helpful for me. Thanks, Jignesh
Re: [GENERAL] What is statement ID of table?
Thanks Andreas. Sorry for confusion here. I mean statement ID that can be associated with prepared query(not table) to improve performance of building query. I just need to find plan using statement ID and execute it. I don't know how to do this. Thanks, Jignesh On Sun, Oct 11, 2009 at 8:06 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Jignesh Shah : Hi, Could any one please tell me what is statement ID of table? There isn't such ID, but every table has an OID, an Object Identifier. How to get it and The (hidden) column oid of pg_class contains this OID. in which scenarios it can be helpful? Any documentation or example about I think, you don't need this, it is only for internal. http://www.postgresql.org/docs/8.4/interactive/datatype-oid.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
