Re: [SQL] sql error creating function
Quoth [EMAIL PROTECTED] ("TJ O'Donnell"):
> When I psql -f f.sql
> I get the following error:
> psql:f.sql:10: ERROR: relation "fragset" does not exist
> CONTEXT: SQL function "fragments"
>
>> cat f.sql
> Create Or Replace Function fragments(character varying)
> Returns setof character varying
> As $$
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1c1');
> Select smiles from fragset;
>
> $$ Language SQL;
>
> But, if I paste into a running psql the commands:
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1c1');
> Select smiles from fragset;
>
> it works fine.
>
> What is wrong in the function definition?
> I'm using 8.1.3
Presumably it's that the query plan is evaluated based on the state of
the database *before* the function runs, at which point table
'fragset' does not exist. Creation of the temp table will work fine,
but the references to it don't, because when the query (to run the
whole thing) is planned, the table doesn't exist.
What you need to do is to generate the various queries on fragset as
dynamic queries, invoked via "execute." That will cause the planner
to run on each statement...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/wp.html
"We believe Windows 95 is a walking antitrust violation"
-- Bryan Sparks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Query response time
Hi all. Thanks for your help so far. However after configuring my system I am still getting major lag times with a couple of queries. The first, which suffers from the '538/539'(see old email included below) bug, is caused by running the following statement: SELECT t1.col1, SUM(test) test_col, SUM(col2) FROM table1 tl, table2 t2 WHERE t1.date BETWEEN '01/10/2004' AND '01/10/2004' AND t1.col3 = t2.col1 AND t1.col5 = t2.col2 AND t2.col3 BETWEEN 50.00 AND 200.00 GROUP BY t1.col1 HAVING SUM(test) BETWEEN 95.00 AND 100.00 ORDER BY 2 DESC, t1.col1; I would like to know if anyone has any ideas why this problem arises. (It's not due to the date format being ambiguous; I have set it to European standard) A second statement I have prepared has the same problem but this time works fine with a limit of 2 but breaks as soon as I set the limit to 3. The offending statement is: SELECT t1.col1, t1.col2, t2.col2, t3.col2 FROM table1 t1, table2 t2, table3 t3, WHERE t1.date BETWEEN '01/10/2004' AND '01/10/2004' AND t3.date = t1.date + 365 AND t2.date = t1.date + 14 AND t1.col1 = t2.col1 AND t1.col1 = t3.col1 And t1.col1 <> '' AND t2.col2 < t1.col2; Both queries work for limited limit sets. But break at different points i.e. enter infinite loops? Does anyone have any ideas? Both queries process fine in Informix! I have run vacuum analyze and full commands on both tables! This didn't solve the problem. Regards, Jonathan -Original Message- Hi all, I am using PostgresSQL 7.4 and having some serious performance issues. Trawling through the archives and previous posts the only visible advice I could see was either by running vacuum or setting the fsynch flag to false. I am using tables that only contain approx 2GB of data. However performing a number of simple conditional select statements takes a great deal of time. Putting limits on the data obviously reduces the time, but there is still a delay. (Note: on one particular query I set the limit to 538 and the query returns in under 2mins if the limit becomes 539 the query loops indefinitely!) From previous experience I know these delays are longer than both Informix and MySql. In some instances it takes so long I end up having to kill the query. The install was performed by yum onto a RAID server using Centos. I am sure there is something fundamentally wrong for I can't believe that postgres would have the reputation it has based on the statistics I'm getting. Does anyone have any advice? The data I am using was imported from an Informix system as part of a migration strategy. I know this is long shot but I hope someone can shed some light. Regards, Jonathan This email may contain information which is privileged or confidential. This information is intended only for the named recipient. If you are not the intended recipient, please be aware that disclosure, copying, distribution or use of this information is prohibited. If you have received this email in error, we would be grateful if you would inform us as soon as possible by telephoning +44 (0) 1769 573431, or by email to [EMAIL PROTECTED] and then delete this email. Views or opinions expressed in this email are those of the writer, and are not necessarily the views of Mole Valley Farmers Limited or its subsidiary companies. Unless specifically stated, this email does not constitute any part of an offer or contract. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Can't find which return type is incorrect.
Alright. I have a very large amount of columns being returned by this stored procedure that I ported from MS SQL to Postgres. Now the problem I'm having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, etc.) is executed the error "wrong record type supplied in RETURN NEXT CONTEXT". Now this immediately red flagged me to look at the types that I was returning and verify that those were the types that I was catching in the as statement. I went through to verify all of the variables and they are all correct as far as both name and the exact type. Therefore there are only a few things left that I'm thinking could be the problem. 1. Too many variables returned (there are 44 variables being returned).2. Some of the variables that are smallint in the select statement also do a if isnull type of logic that will return zero if they are null. (is that zero not coming back as a smallint then?) 3. What I'm declaring as a variable type in postgresql isn't the variable type in PHP. The following are the different types of variables that I use:INTSMALLINTBIGINT (when I do a count(*))VARCHAR(xx) TEXTTIMESTAMPNUMERIC(19,2)Now the two there that I'm skeptical about are the timestamp and the numeric.Thanks ahead of time for any ideas,Chris
[SQL] The Right Way to manage schemas in SCM systems
I've been trying to figure out a good way to manage schema change control for a while now. Since I have a development background, I really want to find some way to check the schema into a SCM system like CVS (for example). Just using a pg_dump doesn't work very well becase there's no guarantee of consistent ordering. One of the things I find most useful with SCM systems is the ability to quickly spot changes. A re-ordering within a single file makes for a lot of noise in the deltas. So far, the best idea I've come up with is the "file tree dump" approach: get a list of every object in the database and then dump it into a suitably named file. Finally, create a master file which consists of only include lines. There are a couple of problems I can see with this. 1) How to calculate the dependancy graph (or worse, dealing with RI loops) to determine the right order to load things in isn't stupidly obvious. 2) If I just script this in bash, without making any changes to pg_dump, it doesn't happen in a single transaction. 3) No clever solution for relative vs absolute filenames (unless all this goes into a tar file, but that format already exists and doesn't solve my problem). So my question is, does anyone have a better way of managing schemas in SCM systems? And if not, is there a precieved value in the community for a pg_dump --format scm_friendly option? (In which case I'll take this thread over to hackers) Drew ---(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: [SQL] Can't find which return type is incorrect.
Can you show us the code for your SP ? I'd like to see what the RETURNS statement is in the sp declaration (CREATE OR REPLACE PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS ...) You might reconsider your SELECT * FROM sproc() AS () -- SELECT * retrieves ALL columns defined by the SP What happens when you drop the "AS (columns)" portion from your select ??? In other words -- what do you get when you simply "SELECT * FROM sp_whatever(1, 0, 3)" ??? ""Chris Lukenbill"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...Alright. I have a very large amount of columns being returned by this stored procedure that I ported from MS SQL to Postgres. Now the problem I'm having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, etc.) is executed the error "wrong record type supplied in RETURN NEXT CONTEXT". Now this immediately red flagged me to look at the types that I was returning and verify that those were the types that I was catching in the as statement. I went through to verify all of the variables and they are all correct as far as both name and the exact type. Therefore there are only a few things left that I'm thinking could be the problem. 1. Too many variables returned (there are 44 variables being returned).2. Some of the variables that are smallint in the select statement also do a if isnull type of logic that will return zero if they are null. (is that zero not coming back as a smallint then?) 3. What I'm declaring as a variable type in postgresql isn't the variable type in PHP. The following are the different types of variables that I use:INTSMALLINTBIGINT (when I do a count(*))VARCHAR(xx) TEXTTIMESTAMPNUMERIC(19,2)Now the two there that I'm skeptical about are the timestamp and the numeric.Thanks ahead of time for any ideas,Chris
Re: [SQL] Can't find which return type is incorrect.
Let me try this againMy first one got delayed when my gmail defualted to the wrong account. (the response is inside codeWarrior's message.Thanks,ChrisOn 8/11/06, Chris Lukenbill <[EMAIL PROTECTED]> wrote: On 8/11/06, codeWarrior < [EMAIL PROTECTED]> wrote: Can you show us the code for your SP ? I'd like to see what the RETURNS statement is in the sp declaration (CREATE OR REPLACE PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS ...)Here is the SPCREATE OR REPLACE FUNCTION sp_content_tree (p_folder int, p_current int, p_maxrows int) RETURNS SETOF RECORD as $$ DECLARE returnRecord RECORD;... FOR returnRecord IN SELECT pseudo_temp_out_simple.levelnum,view_content.*,pseudo_temp_out_simple.children FROM pseudo_temp_out_simple INNER JOIN view_content ON itemID=item ORDER BY pseudo_temp_out_simple.displayorder,view_content.createdate LOOP RETURN NEXT returnRecord; END LOOP; RETURN;END;$$ LANGUAGE plpgsql; You might reconsider your SELECT * FROM sproc() AS () -- SELECT * retrieves ALL columns defined by the SPI'm looking into that currently.. What happens when you drop the "AS (columns)" portion from your select ??? In other words -- what do you get when you simply "SELECT * FROM sp_whatever(1, 0, 3)" ??? I get the..."a column definition list is required for functions returning "record" in"...error. ""Chris Lukenbill"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ...Alright. I have a very large amount of columns being returned by this stored procedure that I ported from MS SQL to Postgres. Now the problem I'm having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, etc.) is executed the error "wrong record type supplied in RETURN NEXT CONTEXT". Now this immediately red flagged me to look at the types that I was returning and verify that those were the types that I was catching in the as statement. I went through to verify all of the variables and they are all correct as far as both name and the exact type. Therefore there are only a few things left that I'm thinking could be the problem. 1. Too many variables returned (there are 44 variables being returned).2. Some of the variables that are smallint in the select statement also do a if isnull type of logic that will return zero if they are null. (is that zero not coming back as a smallint then?) 3. What I'm declaring as a variable type in postgresql isn't the variable type in PHP. The following are the different types of variables that I use:INTSMALLINTBIGINT (when I do a count(*))VARCHAR(xx) TEXTTIMESTAMPNUMERIC(19,2)Now the two there that I'm skeptical about are the timestamp and the numeric.Thanks ahead of time for any ideas,Chris
[SQL] Undo an update
Hello everybody!! Is there a way to undo an update??? Thanks!!! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Undo an update
If it was inside a transaction, and the transaction is still open, then just rollback. Otherwise, no. I'm not sure if there's any way of doing some kind of PITR, no familiar with it. If not, best bet is to recover from the newest backup set you have. Regards, Rodrigo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] timestamp (MS SQLServer's rowversion) functionality
On 10 Aug 2006 06:28:30 -0700, Andrew Hammond <[EMAIL PROTECTED]> wrote: Tomski wrote:> Hello!> As many of you know, SQL Server (2000) has peculiar data type "timestamp"> which is not SQL standard timestamp. In fact it is "rowversion" type. It> makes tha field to be updated with current timestamp when row is updated or > inserted.> Is there any similiar functionality in PostgreSQL? If not, how to achieve> that?> I need such fields in many tables. Maybe triggers could help? Do I have to> write functions for each trigger for each table? Or can it be done by one > function with parameters? Partial or final solutions are welcome :)Create your table with a column of type timestamp and DEFAULT (now())and you have the on insert functionality. You need to use triggers to get the on update fuctionality (and also for inserts if you don't trustthe application to leave it default). I think this is actually coveredby an example in the triggers documentation for postgres. If not then there's certainly a full code solution in the archives of this list.Please do some research before asking questions to the list.I put a create_dt and modify_dt column on every table and set the default to now(). Then I use this trigger: CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF opaque AS'BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN -- assigns the current timestamp -- into the mod_time column NEW.modify_dt := now(); -- displays the new row on an insert/update RETURN NEW; END IF; END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE ON "public"."mytable" FOR EACH ROW EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] timestamp (MS SQLServer's rowversion) functionality
On 8/11/06, Aaron Bono <[EMAIL PROTECTED]> wrote: I put a create_dt and modify_dt column on every table and set the default to now(). Then I use this trigger: CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF opaque AS ' BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN -- assigns the current timestamp -- into the mod_time column NEW.modify_dt := now(); -- displays the new row on an insert/update RETURN NEW; END IF; END; ' LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE ON "public"."mytable" FOR EACH ROW EXECUTE PROCEDURE "public"."modify_date_stamp_fn"(); That's pretty close. Couple of things though. 0) Dollar quoting is readability++ so you might as well get in the habit. 1) Your trigger function should properly return trigger rather than SETOF opaque (but that's a pretty cute hack, I gotta admit). 2) While you're at it, you probably want to enforce the immutability of create_dt on updates. This requires an AFTER trigger. 3) If you're not going to call the function from anything but the insert/update, there's no reason to check if it's and insert or update (unless you want to be paranoid). You're not currently calling it for inserts, but we can change that. 4) This function is properly a security a definer. Not a big deal until (and if) someone decides to implement column level privs. 5) See http://www.postgresql.org/docs/current/static/plpgsql-trigger.html for further documentation. CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS trigger AS $modify_date_stamp$ BEGIN IF TG_OP = ''INSERT'' THEN NEW.create_dt := now(); ELSE IF NEW.create_dt <> OLD.create_dt THEN RAISE EXCEPTION 'Not allowed to change create_dt. Bad programmer!!!'; END IF; -- no changes allowed END IF; NEW.modify_dt := now(); -- always stamp updates RETURN NEW; END; $modify_date_stamp$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; CREATE TRIGGER "mytable_modify_dt_tr" AFTER INSERT OR UPDATE ON "public"."mytable" FOR EACH ROW EXECUTE PROCEDURE "public"."modify_date_stamp_fn"(); Drew ---(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
