[SQL] How can this be legal syntax
I was quite amazed to find that this piece of code actually works while
reviewing code.
I would prefer if it gave an error :)
test=# create or replace function test(i_input text) returns text as
$$
declare
result text;
begin
SELECT
CASE
WHEN lower(i_input) ~ '^[a-z]' THEN 'S'
WHEN i_input ~ '[0-9]' THEN 'N'
ELSE 'ERROR'
INTO result
END;
return result;
end;
$$
language plpgsql security definer;
CREATE FUNCTION
test=# select * from test('' );
test
--
S
(1 row)
test=# create or replace function test(i_input text) returns text as $$
declare
result text;
begin
SELECT
CASE
WHEN lower(i_input) ~ '^[a-z]' INTO result THEN 'S'
WHEN i_input ~ '[0-9]' THEN 'N'
ELSE 'ERROR'
END;
return result;
end;
$$ language plpgsql security definer;
CREATE FUNCTION
test=# select * from test('' );
test
--
S
(1 row)
Re: [SQL] How can this be legal syntax
Asko Oja escribió: > I was quite amazed to find that this piece of code actually works while > reviewing code. > I would prefer if it gave an error :) Yeah, me too. The functions posted by Josh Drake yesterday about constraint exclusion had something like select * from into temp table ...; (temp is a declared variable name). Nasty. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How can this be legal syntax
Alvaro Herrera writes: > Asko Oja escribió: >> I was quite amazed to find that this piece of code actually works while >> reviewing code. >> I would prefer if it gave an error :) > Yeah, me too. As the plpgsql docs say, "The INTO clause can appear almost anywhere in the SQL command". You can write some pretty ugly code if you choose to :-(. Although the docs also threaten to tighten this up, it's been like that for so long that I'm worried about how much user code we'll break if we do. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_clog/0202 Error
Hello every body I just want to know why is happening this to my data base I'm doing the following query: SELECT * FROM pagos where date(fecha_pago) = '2008-12-15'; It returns the next error: ERROR: can't find the transaction status 538976288 DETAIL: can't open file <> not exist file or directory what supposes that I must do?, is the table damaged? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Way to eliminate pg_dump activity from pg_stat_all ?
I've got a bunch of tables in a legacy database that I know are never used, and some more I'm not sure about. So I tried to identify and confirm with: select pg_stat_reset(); -- Wait a long time select * from pg_stat_all_tables where schemaname='public' order by seq_scan,seq_tup_read; select greatest(heap_blks_read,heap_blks_hit),relname from pg_statio_all_tables where schemaname='public' order by 1 desc; But I think I'm getting clutter from the nightly backups. Is there a way to keep pg_dump activity out of the statistics? I can think of several reasons to want such activity excluded, not just this one.
Re: [SQL] Way to eliminate pg_dump activity from pg_stat_all ?
I doubt it. From the server's perspective, pg_dump is just a client executing queries. If the db is never used, why are you continually backing it up? On Dec 16, 2008, at 12:55 PM, Bryce Nesbitt wrote: I've got a bunch of tables in a legacy database that I know are never used, and some more I'm not sure about. So I tried to identify and confirm with: select pg_stat_reset(); -- Wait a long time select * from pg_stat_all_tables where schemaname='public' order by seq_scan,seq_tup_read; select greatest(heap_blks_read,heap_blks_hit),relname from pg_statio_all_tables where schemaname='public' order by 1 desc; But I think I'm getting clutter from the nightly backups. Is there a way to keep pg_dump activity out of the statistics? I can think of several reasons to want such activity excluded, not just this one. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_clog/0202 Error
Judith Altamirano writes: > Hello every body I just want to know why is happening this to my data > base I'm doing the following query: > SELECT * FROM pagos where date(fecha_pago) = '2008-12-15'; > It returns the next error: > ERROR: can't find the transaction status 538976288 > DETAIL: can't open file <> not exist file or directory That looks a whole lot like a corrupt-data problem --- not least because 538976288 = 0x20202020, ie four ASCII spaces. It would seem that something overwrote one of your data rows with text. (The reason you get this message rather than something more useful is that the transaction ID number is the first part of the row data that Postgres can check with any degree of strictness.) If I had to bet I'd bet first on a kernel bug that caused a page of some text file to get dumped into your Postgres data file. It's also possible that you had a hardware-level glitch that led the disk drive to write data in the wrong place. Make sure your system software is up to date, and then run some disk diagnostics. If you don't have a database backup you can revert to, look through the PG list archives for advice about recovering from corrupt data. That particular row is gone beyond recall, but you should be able to clear out the damaged page(s) and at least recover the rest of your table. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] archiving or versioning data?
Hi, I'd like to find a way to archive versions of my data in an elegant and extensible way. When a user modifies certain entries I'd like the database to keep the previous versions (or a limited, definable number of versions). Wiki-style. Would that be a good use of postgres' arrays? So I'm looking for "best practices" (tm) on that subject. Thanks in advance for your suggestions, -- http://www.critikart.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] archiving or versioning data?
In response to Louis-David Mitterrand : > Hi, > > I'd like to find a way to archive versions of my data in an elegant and > extensible way. > > When a user modifies certain entries I'd like the database to keep the > previous versions (or a limited, definable number of versions). > Wiki-style. > > Would that be a good use of postgres' arrays? > > So I'm looking for "best practices" (tm) on that subject. > > Thanks in advance for your suggestions, You can use tablelog: 08:11 < akretschmer> ??tablelog 08:11 < pg_docbot_adz> For information about 'tablelog' see: 08:11 < pg_docbot_adz> http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html 08:11 < pg_docbot_adz> http://pgfoundry.org/projects/tablelog/ It logs all modifications for a particular table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
