Hi All, I have been tinkering with a function to log the changes made on
any column through a function and trigger. However, I cant think of a way
to make this work through pl/pgsql. Any one have any ideas, or is it just
not possible?
SNIP
create or replace function logchange2() returns OPAQUE a
Region AS R
LEFT JOIN City AS Ci
LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id
ON R.id = C.region_id
GROUP BY
R.region_name;
Gavin.
On 17/06/2009, at 7:25 AM, Richard Broersma wrote:
On Tue, Jun 16, 2009 at 1:59 PM, Carol
Cheung wrote:
I would like to find the
_timestamp(time) AS ts, extract('months',to_times...
I also tried doing the extract on the alias "ts".
Am I doing something wrong here? Is this possible or do I need to approach
it in a different way? I've looked through the manual but I can't see a
way to conve
On Thu, 17 Sep 2009, Frank Bax wrote:
> Gavin McCullagh wrote:
>> SELECT time, to_timestamp(time) AS ts,
>> EXTRACT('months',to_timestamp(time)) FROM mdl_log;
>> ERROR: syntax error at or near ","
>> LINE 1: ...t time, to_timestamp(time) AS ts,
On Thu, 17 Sep 2009, Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Frank Bax wrote:
>
> > Gavin McCullagh wrote:
> >> SELECT time, to_timestamp(time) AS ts,
> >> EXTRACT('months',to_timestamp(time)) FROM mdl_log;
> >> ERROR: syntax error at or n
his works fine.
SELECT extract(month from to_timestamp(time))
FROM mdl_log;
Many thanks,
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
_timestamp(time) AS ts, extract('months',to_times...
I also tried doing the extract on the alias "ts".
Am I doing something wrong here? Is this possible or do I need to approach
it in a different way? I've looked through the manual but I can't see a
way to conve
- thanks in advance for any assistance you might be able to give us!
Gavin.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ng.
I also read chapters 15 / 24 of the excellent documentation about running both
8.1 and 9.04 at the same time and migrating a database at a time.
So, I suppose unless there is something obvious, (to someone else) - we have
all the information we need.
Gavin "Beau" Baumanis
On 17/0
How about this (that does not require special functions nor triggers:
DROP TABLE IF EXISTS val;
CREATE TABLE val
(
id int,
ts timestamp
);
INSERT INTO val
VALUES
(0, '1-Jan-2010 20:00'),
(1, '1-Jan-2010 20:03'),
(1, '1-Jan-2010 20:04'),
(0, '1-Jan-2010 20:05'),
On 06/07/11 01:52, John Fabiani wrote:
Hi,
I have a special need to create a sequence like function.
"O-20110704 -2" which is
"O" for order (there are other types)
"20110704" is for July 4, 2011
'2' the second order of the day for July 4, 2011
I of course can get the type and date. What I do
On 06/07/11 21:47, Gavin Flower wrote:
I forgot the format required of the order number, so to get the full
yesr, I should have used:
to_char(day, 'MMDD')
[...]
v_order_num := type::text ||
'-' ||
27;),
(2, 'b');
do $$
declare
v_sql_querytext;
v_sql_explain text;
v_result text;
begin
v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\'';
v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query;
execute v_sql_explain into v_result;
raise notice 'v_result: %', v_result;
end;
$$ language plpgsql;
Cheers,
Gavin
mont(1);
SELECT sort_mont(12);
/// output..
gavin=> \i modulus_stored_proc.sql
DROP FUNCTION
CREATE FUNCTION
sort_mont
---
10
(1 row)
sort_mont
---
9
(1 row)
gavin=>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make cha
some 45 or more characters!
I normally use integers for the primary key type. This makes keeping
track of records in a program much easier.
However, I do not usually expose these keys to users, and it would be
rare (if ever) to have them as fields in search boxes.
Cheers,
Gavin
3 * random(),
generate_series(1, 5));
SELECT
*
FROM
tabc t
ORDER BY
t.a,
LEAST(t.b, t.c),
t.d
/**/;/**/
gavin=> \i variable_sort_order.sql
DROP TABLE
psql:variable_sort_order.sql:10: NOTICE: CREATE TABLE will create
implicit sequence "tabc_id_seq" for
approach
(N.B. The indexing of the period table here, can be used in my previous
solution where I had not considered the indexing seriously!)
Cheers,
Gavin
DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS target;
CREATE TABLE period
(
start_date date,
end_datedate,
PRIMARY KEY
, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
Thanks,
Anton
How about something like the following?
Cheers,
Gavin
DROP
max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
Cunning, also much more elegant and concise than my solutions!
Cheers,
Gavin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
operating system.
Cheers,
Gavin
On 28/12/12 05:44, John Fabiani wrote:
On 12/27/2012 08:21 AM, Gavin Flower wrote:
On 28/12/12 03:27, John Fabiani wrote:
Hi,
I have the following statement in a function.
UPDATE orderseq
SET orderseq_number = (orderseq_number + 1)
WHERE (orderseq_name='InvcNumber');
A
what research you read. Fortunately, as far as I know, no one in my
immediate family falls into this group.
Cheers,
Gavin
at answering these questions will have no direct
bearing on how people will answer your query! :-)
Cheers,
Gavin
"other" tasks based on if a record was
returned or not is proving a little difficult in this instance.
We are using 8.1.9 on Debian.
Thanks for any help you might be able to offer.
--
If I can ever be of any further assistance, please contact me.
Gavin 'Beau' Baumanis
eClini
match the
encoding expected by the server, which is controlled by "client_encoding".
I understand the error message - but what I don't know is what I need to set
the encoding to - in order to import / use the data.
As always - thanks in advance for any help you might be able
Hi and thanks for the replies,
I have had some luck.
I did find the encoding used originally to create the text files I am trying to
import.
I have managed to use the client_encoding environmental variable and then
successfully did manage to import the data.
Gavin.
On 12/02/2011, at 8:15
That way you have "local" access to the DB and all of it's tools too.
You can "play around" with it to your hearts content, run SQL
"scenarios" / test functions etc on your own copy of the DB.
Then when you're happy - perform the "locally tested" SQL
an
you direct them to provide the things I need?".
Bart Degryse wrote:
>>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 12:46 >>>
>The windows installer, available at;
>http://www.postgresql.org/ftp/win32/
>Allows you to install the DB an
creating a new record.
Thanks in advance for anything you might come up with.
Warmest regards,
Gavin Baumanis
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
same table.
I am happy enough to give you a table schema, if that's required...
but I just don't see why it would be needed - but of course am happy
to be told something new!
Thanks again.
Gavin Baumanis
On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote:
On Thu, Mar 20, 2008 at 4:
Hi Everyone,
I want to thank everyone for their help / suggestions...
I really appreciate it.
Though I think I have found a winner.
craig=# update x set val = foundrow.val from ( select val from x
where id = 2 ) as foundrow where id = 1 ;
UPDATE 1
Very elegant, very clean...
Very nice!
T
;2008-04-10'} format
as being a valid date data type...
But it works elsewhere in the application / on other sites - thus my
current state of confusion.
Thanks in advance for any ideas you might have.
Please contact me if you should have any questions.
Gavin 'Beau' Baumanis
01-23'}
The weirdness for me is that it works elsewhere in the application, I
am really stumped.
-Gavin.
On 10/04/2008, at 4:17 PM, Aarni Ruuhimäki wrote:
On Thursday 10 April 2008 03:14, Gavin 'Beau' Baumanis wrote:
Hi Everyone,
I have a question about dates, please.
I
ht be able to provide me with!
select
(select
count(*)
from
myTable as myCount
where
myCount.contactdate <= myTable.contactdate
) as rownum,
contactdate
from
myTable
where
contactdate > '2007-06-30 23:59:59'
order by
Hi Everyone,
I am hoping that you might be able to give me some assistance with the
following task!
I have a database with nearly 200 tables and I need to find all tables
that contain a column of myColumnName.
I was hoping there might be a built-in function for this task, but I
have been
08 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote:
I am hoping that you might be able to give me some assistance with
the
following task!
I have a database with nearly 200 tables and I need to find all
tables
that contain a column of myColumnName.
select * from information_schem
Hi Everyone,
I am having some issues trying to create the required SQL that will
allow me to return the results I am after.
I have been trying various incarnations, using group by, sub-queries -
albeit to no avail - for the past three hours.
Consider the following simple SQL;
select
a
, Tom Lane wrote:
"Gavin 'Beau' Baumanis" <[EMAIL PROTECTED]> writes:
... If there a multiple rows of the same id in table1, I get all
(multiple) rows - as you would expect - of course.
What I need however, is only one row returned per instance a.id that
is returned
38 matches
Mail list logo
