[GENERAL] permission denied for relation
while browsing a web site (which I am trying to handle), I get an error message like this: * Warning*: pg_query() [function.pg-query]: Query failed: ERROR: permission denied for relation -table name- in... on line 45 I believe it has a connection to upgrading to postgresql8 , but I am not sure about it. Can anyone advice what to do about it. it wasn't there before. thanks Armon ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] permission denied for relation
Armon Ezra [EMAIL PROTECTED] schrieb: while browsing a web site (which I am trying to handle), I get an error message like this: * Warning*: pg_query() [function.pg-query]: Query failed: ERROR: permission denied for relation -table name- in... on line 45 I believe it has a connection to upgrading to postgresql8 , but I am not sure about it. Can anyone advice what to do about it. it wasn't there before. Without knowing more details it is hard to help you. Check, if the user has the necessary rights on this table. You can use psql for this, \dp tablename or \z tablename show all information. You can assign rights with GRANT. 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.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing support for python on windows
On Sunday 31 December 2006 7:06 pm, novnov wrote: Thanks to both of you. I tried the following and got an error that plpython.dll couldn't be found: D:\postgresql\bincreatelang -U sauser plpythonu mydb Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. The file spec'd by the error message does indeed exist, though the slashes in windows would of course be the other way around in normal use. Then I tried leaving of the u in plpythonu D:\postgresql\bincreatelang -U sauser plpython mydb Password: createlang: language installation failed: ERROR: unsupported language plpython HINT: The supported languages are listed in the pg_pltemplate system catalog. Then I tried something like what Adrian had suggested: D:\postgresql\bincreatelang -U sauser -d mydb plpythonu Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. It's interesting that createlang knows to look in the peer lib directory for the language file but somehow does not like the plpython that it sees there. I also tried plpython (no trailing u) and had the same error as the earlier experiment. At this point I don't know what to say. Hopefully someone with with experience installing Postgres on Windows can help out. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing support for python on windows
novnov wrote: Then I tried something like what Adrian had suggested: D:\postgresql\bincreatelang -U sauser -d mydb plpythonu Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. It's interesting that createlang knows to look in the peer lib directory for the language file but somehow does not like the plpython that it sees there. I also tried plpython (no trailing u) and had the same error as the earlier experiment. Most likely it's not finding the require python DLLs in the system PATH, just the plpython one. Make sure that your actual python directory is in the PATH, add it if not. If it is, use the depends tool (part of the support tools) to verify exactly what DLL it's not finding. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing support for python on windows
This is so much more difficult than I imagined it could be. I've added the main python dir and the lib dir to my path and nothing has changed. I may be able to figure out how to use the depends tool, so far it looks pretty obscure to a newb. This is all being done on a new workstation. It is a little puzzling that straight up installs of postgres and python result in a config that doesn't respond to the supposedly simple one line command to add python support. I've read about this for hours and it does not normally appear to be a problematic process. Thanks to both of you for trying to help. Magnus Hagander-2 wrote: novnov wrote: Then I tried something like what Adrian had suggested: D:\postgresql\bincreatelang -U sauser -d mydb plpythonu Password: createlang: language installation failed: ERROR: could not load library D:/pos tgresql/lib/plpython.dll: The specified module could not be found. It's interesting that createlang knows to look in the peer lib directory for the language file but somehow does not like the plpython that it sees there. I also tried plpython (no trailing u) and had the same error as the earlier experiment. Most likely it's not finding the require python DLLs in the system PATH, just the plpython one. Make sure that your actual python directory is in the PATH, add it if not. If it is, use the depends tool (part of the support tools) to verify exactly what DLL it's not finding. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend -- View this message in context: http://www.nabble.com/Installing-support-for-python-on-windows-tf2902841.html#a8116019 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing support for python on windows
novnov wrote: This is so much more difficult than I imagined it could be. I've added the main python dir and the lib dir to my path and nothing has changed. I may be able to figure out how to use the depends tool, so far it looks pretty obscure to a newb. It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with index in OR'd expression
Ragnar wrote: Reguardless of the issue whether pl/pgsql could be expected to optimize this case, I find it difficult to imagine a scenario where this kind of coding makes sense. I understand that in some cases on would like to do this with a *variable* to simplify logic, but what possible gain can be had from doing this with a constant, other that obfuscation? Well, in one way it's a variable, but in another a constant. It's a variable in the context of general PG usage... e.g., my application code may call the function with whatever parameters a user chooses, leaving some parameters null and others not. Within the context of the function (after calling), these variables are constant and I'm attempting to use my OR syntax as shorthand to avoid having to use a dynamic statement *only* because of this situation. As I've mentioned, this approach seems to work with MSSQL 6.5+, which I assume we consider as a valid competitor to PG... if this didn't work anywhere else, I probably wouldn't even have brought it up. I'll re-iterate another question I attempted to pose which was: what have other PG application developers done in this situation? Is it most common to just use dynamic statements? Thanks for your response. jl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] spooky refusal to insert
postgresql 8.1, fedora core 4
I'm trying to update a database with a few new tables and insert some
data. However, psql is refusing to insert some of the data, leading to
errors when trying to refer to the sequence in the next insert (to a
cross table).
Here are the new tables (Note that set_id() and get_id() are functions
which use the $_SHARED structure so that i can set some vars and refer
back to them later)
First, i set up the new tables, inserting some data into the look-up
table for grant types.
-- snip --
DROP TABLE funding_type CASCADE;
CREATE TABLE funding_type (
id SERIAL PRIMARY KEY,
name VARCHAR(16) NOT NULL
);
INSERT INTO funding_type (name) VALUES ('Grant');
SELECT set_id('Grant ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Award');
SELECT set_id('Award', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Residency');
SELECT set_id('Residency ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Special');
SELECT set_id('Special ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Other');
SELECT set_id('Other ', CAST(currval('funding_type_id_seq') AS INT));
-- this is the problem table. It
-- gets created, but nothing inserts
DROP TABLE arts_funder CASCADE;
CREATE TABLE arts_funder (
id SERIAL PRIMARY KEY,
name VARCHAR(256) NOT NULL,
fund_name VARCHAR(128),
funding_type_id INT4 NOT NULL,
amount TEXT,
short_description TEXT,
long_description TEXT,
eligibility TEXT,
deadline VARCHAR(256),
website VARCHAR(256),
phone VARCHAR(256),
disclaimer TEXT,
CONSTRAINT
fk_arts_funder_funding_type FOREIGN KEY (funding_type_id)
REFERENCES funding_type
ON DELETE CASCADE
);
-- this table is fine
DROP TABLE arts_funder_discipline CASCADE;
CREATE TABLE arts_funder_discipline (
arts_funder_id INT4 NOT NULL,
discipline_id INT4 NOT NULL,
CONSTRAINT
fk_arts_funder_discipline_arts_funder_id FOREIGN KEY (arts_funder_id)
REFERENCES arts_funder
ON DELETE CASCADE,
CONSTRAINT
fk_arts_funder_discipline_discipline_id FOREIGN KEY (discipline_id)
REFERENCES discipline
ON DELETE CASCADE
);
-- snip --
The inserts come next in the SQL file i'm using as input. But i should
point out this first before going on:
-- snip --
test=# \d arts_funder
Table public.arts_funder
Column | Type |
Modifiers
---++--
id| integer| not null default
nextval('arts_funder_id_seq'::regclass)
body | character varying(256) | not null
fund_name | character varying(128) |
funding_type_id | integer| not null
amount| text |
short_description | text |
long_description | text |
eligibility | text |
deadline | character varying(256) |
website | character varying(256) |
phone | character varying(256) |
disclaimer| text |
Indexes:
arts_funder_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
fk_arts_funder_funding_type FOREIGN KEY (funding_type_id)
REFERENCES funding_type(id) ON DELETE CASCADE
-- snip --
So, looks good to me.
The inserts into both arts_funder and arts_funder_discipline look like so:
-- snip --
INSERT INTO arts_funder (name, fund_name, funding_type_id, amount,
short_description, long_description, eligibility, deadline, website,
phone, disclaimer)
VALUES ('The Canada Council for the Arts', 'Artists and Community
Collaboration Fund (ACCF)', CAST(get_id('Grant') AS INT), 'Varies',
'This program provides support for developing projects that bring
together professional artists and communities to give creative arts a
stronger presence in everyday life.', 'pThe goal of the ACCF is to
give creative arts a stronger presence in everyday life. The ACCF
provides funding for creative collaborations between communities and
artists. The collaborative process may include developing projects of a
diverse nature; projects that involve youth and arts education are
encouraged. The ACCF is implemented through the participating programs
in all sections of the Canada Council./p', 'pPlease contact the
Canada Council (and the program office of the discipline that interests
you) for detailed eligibility requirements./p', 'Varies',
'www.canadacouncil.ca', '1-800-263-5588 (toll free)br /(613)
566-4414br /TTY (TDD) machine for hearing-impaired callers: (613)
565-5194', 'Please contact Canada Council directly for program details.');
-- each of the inserts into arts_funder is followed by
-- one or more into arts_funder_discipline, which is just
-- a cross table. I get errors here because psql thinks
Re: [GENERAL] spooky refusal to insert
That should be version 8.1.4 -- sorry brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Installing support for python on windows
On Monday 01 January 2007 11:14 am, Magnus Hagander wrote: novnov wrote: This is so much more difficult than I imagined it could be. I've added the main python dir and the lib dir to my path and nothing has changed. I may be able to figure out how to use the depends tool, so far it looks pretty obscure to a newb. It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match I broke down and installed Postgres on Windows. I had the same problem with installing plpython until I did as Magnus suggested, rebooted Windows. I then ran createlang -d template1 -U postgres plpythonu to install it into the template1 database and it succeeded. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with index in OR'd expression
On mán, 2007-01-01 at 14:21 -0600, [EMAIL PROTECTED] wrote: Within the context of the function (after calling), these variables are constant and I'm attempting to use my OR syntax as shorthand to avoid having to use a dynamic statement *only* because of this situation. As I've mentioned, this approach seems to work with MSSQL 6.5+, which I assume we consider as a valid competitor to PG... if this didn't work anywhere else, I probably wouldn't even have brought it up. I'll re-iterate another question I attempted to pose which was: what have other PG application developers done in this situation? Is it most common to just use dynamic statements? ps/pgsql will prepare the statement caching the plan for subsequent calls, thus making this not easily optimized. to avoid this in ps/pgsql , I believe it is common to to make the statement dynamic by using EXECUTE, thus in effect making sure the query is planned each time. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to convert money columns to numeric?
I want to convert a column named amount, currently of type money, to type numeric(10,2). When I try to do this using: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2); I get: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So then I figure I need to do it with SQL of the form: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING expression; But I can't find a conversion function or operator that will accept a money column as input. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING to_number(amount, '.99'); Evokes this error message: PostgreSQL Error Code: (1) ERROR: function to_number(money, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. And I can't seem to cast a money column into anything else. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING cast(amount as numeric); Evokes: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So I'm fresh out of ideas - other than dropping and recreating the column, which would lose a lot of data. ~ TIA ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] spooky refusal to insert [SOLVED]
brian wrote:
postgresql 8.1, fedora core 4
I'm trying to update a database with a few new tables and insert some
data. However, psql is refusing to insert some of the data, leading to
errors when trying to refer to the sequence in the next insert (to a
cross table).
Sure, less than ten minutes since i sent out my plea, i figured it out
for myself. For the curious:
-- snip --
DROP TABLE funding_type CASCADE;
CREATE TABLE funding_type (
id SERIAL PRIMARY KEY,
name VARCHAR(16) NOT NULL
);
INSERT INTO funding_type (name) VALUES ('Grant');
SELECT set_id('Grant ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Award');
SELECT set_id('Award', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Residency');
SELECT set_id('Residency ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Special');
SELECT set_id('Special ', CAST(currval('funding_type_id_seq') AS INT));
INSERT INTO funding_type (name) VALUES ('Other');
SELECT set_id('Other ', CAST(currval('funding_type_id_seq') AS INT));
Note the extra spaces after the variable names i'm using:
set_id('Grant ', ...
Because psql was not writing the errors to the file, i was relying on
what i saw in my terminal. The very last insert into arts_funder was
followed by 10 subsequent inserts into arts_funder_discipline. I'd
missed the very first error:
psql:funders.sql:1171: ERROR: null value in column funding_type_id
violates not-null constraint
Which is the error on insert into arts_funder. The construct
CAST(get_id('Grant') AS INT) was returning NULL because of the
whitespace, above.
I'm still confused as to why errors are not written to the output file
(\o out.txt). There must be some way to capture these, aside from a
quickly scrolling terminal window.
brian
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Installing support for python on windows
Adrian Klaver wrote: On Monday 01 January 2007 11:14 am, Magnus Hagander wrote: novnov wrote: This is so much more difficult than I imagined it could be. I've added the main python dir and the lib dir to my path and nothing has changed. I may be able to figure out how to use the depends tool, so far it looks pretty obscure to a newb. It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match I broke down and installed Postgres on Windows. I had the same problem with installing plpython until I did as Magnus suggested, rebooted Windows. I then ran createlang -d template1 -U postgres plpythonu to install it into the template1 database and it succeeded. -- Adrian Klaver [EMAIL PROTECTED] That's encouraging. But I rebooted as a part of my original testing after adding to path, and just now again. I tried with your -d syntax too and still the same error. I'll try depend next. -- View this message in context: http://www.nabble.com/Installing-support-for-python-on-windows-tf2902841.html#a8118133 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to convert money columns to numeric?
On Monday 01 January 2007 1:45 pm, Ken Winter wrote: I want to convert a column named amount, currently of type money, to type numeric(10,2). When I try to do this using: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2); I get: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So then I figure I need to do it with SQL of the form: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING expression; But I can't find a conversion function or operator that will accept a money column as input. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING to_number(amount, '.99'); Evokes this error message: PostgreSQL Error Code: (1) ERROR: function to_number(money, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. And I can't seem to cast a money column into anything else. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING cast(amount as numeric); Evokes: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So I'm fresh out of ideas - other than dropping and recreating the column, which would lose a lot of data. ~ TIA ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Take a look at the GeneralBits column below for a possible solution(see heading Convert money type to numeric)- http://www.varlena.com/GeneralBits/75.php -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing support for python on windows
It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. plpython.dll is in the lib dir. But at a command prompt there, depends plpython.dll just gets me 'depends' is not recognized as an internal or external command etc. The postgres docs talk about the catalog pg_depend, and maybe 'depends' is another executable that calls that, but I've not been able to find a 'depends.exe'. depends does not seem to fly in psql. The pg_depends docs don't really tell me how to use pg_depends on it's own...I don't know anything about catalogs, or it'd probably be obvious. The server was restarted and the path modified was for the server. Thanks -- View this message in context: http://www.nabble.com/Installing-support-for-python-on-windows-tf2902841.html#a8118252 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing support for python on windows
On Monday 01 January 2007 3:39 pm, novnov wrote: It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. plpython.dll is in the lib dir. But at a command prompt there, depends plpython.dll just gets me 'depends' is not recognized as an internal or external command etc. The postgres docs talk about the catalog pg_depend, and maybe 'depends' is another executable that calls that, but I've not been able to find a 'depends.exe'. depends does not seem to fly in psql. The pg_depends docs don't really tell me how to use pg_depends on it's own...I don't know anything about catalogs, or it'd probably be obvious. The server was restarted and the path modified was for the server. Thanks Depends is a windows program. To get it I had to load the Windows support tools from the Windows install disc(in my case the image on my harddrive). -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installing support for python on windows
Thanks, the depends tools looks very handy, surprising I'd not heard of it before. I found that the postgresql\bin dir must be added to the path. Also, I had python 2.5 installed, and plpython apparently needs python 2.4. I've installed that and added to the path, but there is another dependency missing inside of the python stack, DWMAPI.dll. Googling DWMAPI.dll gets a mixed bag, but I think that it might be part of IE6, and not IE7 (I have IE7). Maybe the current plpython does not work unless IE6 is installed, because plpython needs python 2.4, which needs IE 6??? Adrian Klaver wrote: On Monday 01 January 2007 3:39 pm, novnov wrote: It should be easy enough - just run depends plpython.dll in the directory where plpython is. Two things to verify first: 1) Verify that you added the directories to the system path, and not your personal path 2) Did you restart the server after adding it? Needs to be done for windows to pick up the change in environment. plpython.dll is in the lib dir. But at a command prompt there, depends plpython.dll just gets me 'depends' is not recognized as an internal or external command etc. The postgres docs talk about the catalog pg_depend, and maybe 'depends' is another executable that calls that, but I've not been able to find a 'depends.exe'. depends does not seem to fly in psql. The pg_depends docs don't really tell me how to use pg_depends on it's own...I don't know anything about catalogs, or it'd probably be obvious. The server was restarted and the path modified was for the server. Thanks Depends is a windows program. To get it I had to load the Windows support tools from the Windows install disc(in my case the image on my harddrive). -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- View this message in context: http://www.nabble.com/Installing-support-for-python-on-windows-tf2902841.html#a8119308 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] regular expression limit
I believe there's been a change in PostgreSQL's regular expression
handling w/ 8.2.
CREATE TABLE testb (
name
TEXT
--CHECK( name ~ '^[a-f0-9]{1,256}$' )
CHECK( name ~ '^[a-f0-9]{1,255}$' )
);
If I swap the two check statements above, I can no longer insert data.
The operation errors out with:
invalid regular expression: invalid repetition count(s)
I'd like the following domain statement to work. It used to work in
8.1.4, but not now. Can I do this in 8.2?
CREATE DOMAIN
__hex_string_8192
AS TEXT
CHECK ( VALUE ~ '^[a-f0-9]{1,8192}$' );
TIA.
--
Ron Peterson
https://www.yellowbank.com/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] spooky refusal to insert
On 1/2/07, brian [EMAIL PROTECTED] wrote: Here are the new tables (Note that set_id() and get_id() are functions which use the $_SHARED structure so that i can set some vars and refer back to them later) Hi Brian, Can you please explain the $_SHARED structure you mention here? I am not able to find documentation for it!! If I understand it correctly, it probably is a structure stored in the backend, and the plpgsql functions can use it to store values across multiple calls; like C global variables. Regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] spooky refusal to insert
Gurjeet Singh wrote:
On 1/2/07, brian [EMAIL PROTECTED] wrote:
Here are the new tables (Note that set_id() and get_id() are functions
which use the $_SHARED structure so that i can set some vars and refer
back to them later)
Hi Brian,
Can you please explain the $_SHARED structure you mention here? I am not
able to find documentation for it!!
If I understand it correctly, it probably is a structure stored in the
backend, and the plpgsql functions can use it to store values across
multiple calls; like C global variables.
Certainly. I'd been about to explain it further, however my initial cry
for help was already dragging on quite a bit so i left it at that.
$_SHARED is a global hash in PL/Perl. I found these two functions in the
docs:
39.4. Global Values in PL/Perl
http://www.postgresql.org/docs/8.2/static/plperl-global.html
You can find more info about using $_SHARED to communicate between
functions here:
http://www.oreillynet.com/pub/a/databases/2006/05/25/the-future-of-perl-in-postgresql.html
With these two functions, you pass the hash key you want to use, along
with the value you wish to store. The if() test is assigning it first,
so if, for some reason it cannot be set, if() will fail. You can, of
course, change the return value to anything you'd like. The original is
fine for my needs.
I generally use these to grab the current value of the sequence for some
look-up table as it is being added to. Things like sports teams,
countries, etc. Generally, tables with data that doesn't change a whole
lot, and are used primarily for relating between other tables.
So, i can use that later when i need to cross-reference some other
value. In this case, it was relating the arts_funders with specific arts
disciplines, as well as with funding types, as i added them to the database.
If you'd like a more concrete example, i'd be happy to send that.
/**
* Store IDs from lookup tables
*
* @param text name the hash key (lookup table name entry)
* @param int val the lookup table row id
* @returns text success or not
**/
CREATE OR REPLACE FUNCTION set_id(name TEXT val INT4) RETURNS TEXT AS
$$
if ($_SHARED{$_[0]} = $_[1])
{
return 'ok';
}
else
{
return can't set shared variable $_[0] to $_[1];
}
$$ LANGUAGE plperl;
/**
* Retrieve ID from lookup table
*
* @param text name the hash key
* @returns int the hash value
**/
CREATE OR REPLACE FUNCTION get_id(name text) RETURNS INT4 IMMUTABLE AS
$$
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;
Note that this last function will return NULL if the key does not exist
(which was the root of my problem, as the keys initially had extra
whitespace--oops!)
regards,
brian
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
