Re: [SQL] database abstraction -> functions
Jeroen Olthof wrote: > Hi, > > When developing applications is a good thing to create abstraction between > different layers > The one concerning the database would be the persistence layer. To create > such abstraction I want all mij datababase activitie runned through > functions. But how can I return a set of rows instead of a single datatype > (I looked into the setof but never found a clear simple)? > A very simple answer - there is no way to do what you want :-( They promise to have it in 7.3... I am using a workaround, where my functions glue all the columns together into a pipe-separated line, that I then parse back into columns in my application... Also, note that, unless your functions are in C (anything like plpgsql, or just sql), they are slow like hell... In that case, you are much better off implementing your abstraction layer through views... Dima ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is there a better way than this to get the start and end of a month?
Well, month_end could be more straightforward as something like
select month_start ($1) + interval '1 day' - interval '1 month';
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start (date)
> RETURNS date
> AS '
>DECLARE
> day ALIAS FOR $1;
>BEGIN
> RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
> ''1 day''::interval;
>END;
> '
> LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION month_end (date)
> RETURNS date
> AS '
>DECLARE
> day ALIAS FOR $1;
> month int;
> year int;
>BEGIN
> month := extract(''month'' FROM day);
> year := extract(''year'' FROM day);
> IF month = 12 THEN
> month := 1;
> year := year +1;
> ELSE
> month := month +1;
> END IF;
> RETURN (''01-''||month||''-''||year)::date -
> ''1 day''::interval;
> END;
>'
>LANGUAGE 'plpgsql';
>
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Is there a better way than this to get the start and end of a month?
Sorry, the previous message was wrong...
This is better:
create function month_start (date) as
'select date_trunc ('month', $1)::date;'
language 'sql';
create function month_end (date) as
'select month_start ($1) - 1 + interval '1 month';
language 'sql';
I hope, it helps...
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start (date)
> RETURNS date
> AS '
>DECLARE
> day ALIAS FOR $1;
>BEGIN
> RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
> ''1 day''::interval;
>END;
> '
> LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION month_end (date)
> RETURNS date
> AS '
>DECLARE
> day ALIAS FOR $1;
> month int;
> year int;
>BEGIN
> month := extract(''month'' FROM day);
> year := extract(''year'' FROM day);
> IF month = 12 THEN
> month := 1;
> year := year +1;
> ELSE
> month := month +1;
> END IF;
> RETURN (''01-''||month||''-''||year)::date -
> ''1 day''::interval;
> END;
>'
>LANGUAGE 'plpgsql';
>
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Timestamp Error - 7.2
Keith,
> We have moved from 7.1 to 7.2 and get the following error when
> extracting dates.
>
> Bad timestamp external representation ' '
>
>
> eg. INSERT INTO mytable VALUES('1', '2001-09-24')
The error, as you have described it, is highly unlikely. You will
need to provide more information if you still want help. Please:
1. Post table defintions.
2. Post your *actual* SQL statements which trigger the error, not
example ones which you have nto actually tested.
-Josh Berkus
---(end of broadcast)---
TIP 3: 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: [SQL] Getting current transaction id
Michael Paesold <[EMAIL PROTECTED]> writes: > I just read it's possible to get the MVCC last version numbers. Is it also > possible to get the current transaction id? Well, there's the brute force way: insert a tuple in some table and look at its xmin. Offhand I don't think we provide a SQL function to read current transaction id, though it'd surely be a trivial addition. > Would it be possible to check > later if that transaction has been commited? This would be nice for a distributed > application to enforce an "exactly once" semantics for transactions (even if > there are network related errors while the server sends ack for commiting a > transaction). Again, it's not an exported operation, though you could add a SQL function that called TransactionIdDidCommit(). > And if it's possible, how long would that information be valid, i.e. when do > transaction id's get reused? That would be the tricky part. The ID would be reused after 4 billion transactions, which is long enough that you probably don't care ... but the segment of the transaction log that has the associated commit bit will be recycled as soon as the server has no internal use for it anymore, which could be as early as the next database-wide VACUUM. If you tried to call TransactionIdDidCommit() after that, you'd get the infamous "can't open pg_clog/" error. > If it's not working I will have to implement my own transactions table. That's what I'd recommend. Transaction IDs are internal to the database and are not designed for users to rely on. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL formatter?
Does anyone know of a routine for formatting SQL statements in a structured way? Standalone or for emacs would be fine. I'm thinking of something that could take a long SQL text statement and format it, e.g.: select foo from bar where baz and bop and not boo; becomes SELECT foo FROM bar WHERE baz AND bop AND NOT boo ; Thanks, Andy -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Getting current transaction id
Tom Lane wrote: > Michael Paesold <[EMAIL PROTECTED]> writes: [snip] > > If it's not working I will have to implement my own transactions table. > > That's what I'd recommend. Transaction IDs are internal to the database > and are not designed for users to rely on. > > regards, tom lane Well, after reading your explanation I agree with you that it is better to have my own transaction table. I appreciate your detailed response. Thanks very much! Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL formatter?
Looks to be windows based, but... http://www.techno-kitten.com/PBL_Peeper/Online_Manual/SQL_Formatter/sql_formatter.html first hit when searching on google for "sql formatter". there were a lot of other options... You might look at how some of those C code indenter's work. Seems like some of them support multiple languages which means they maybe have some sort of "language definition" so maybe you could just write a sql one and it would just work. Of course I've never used one and don't know anything about it really so I could be wrong :) -philip On Wed, 25 Sep 2002, Andrew Perrin wrote: > Does anyone know of a routine for formatting SQL statements in a > structured way? Standalone or for emacs would be fine. I'm thinking of > something that could take a long SQL text statement and format it, e.g.: > > select foo from bar where baz and bop and not boo; > > becomes > > SELECT foo > FROM bar > WHERE baz >AND bop >AND NOT boo > ; > > Thanks, > Andy > > -- > Andrew J Perrin - http://www.unc.edu/~aperrin > Assistant Professor of Sociology, U of North Carolina, Chapel Hill > [EMAIL PROTECTED] * andrew_perrin (at) unc.edu > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
