Re: [SQL] Scalar in a range (but textual not numeric)

[Richard Huxton]

On Wednesday 25 February 2004 22:37, Tom Lane wrote:
> I wrote:
> > try writing
> > WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max
> >   AND pr_min < (SELECT pr_min FROM table
> > WHERE pr_min > 'ABCDE'
> > ORDER BY pr_min LIMIT 1)
> > The idea here is to add an upper bound on pr_min to the index scan
> > conditions, so that the scan can stop short of the end of the index.
>
> Argh, got that backwards.  What you are missing is a *lower* bound on
> pr_min, and the index scan will therefore run from the start of the
> index up to pr_min = 'ABCDE'.  So reverse the sense of the added test:
>
> AND pr_min >= (SELECT pr_min FROM table
>WHERE pr_min <= 'ABCDE'
>ORDER BY pr_min DESC LIMIT 1)

Aha! I was trying something like that, but couldn't get it quite right and it 
was getting too late for me to see clearly.

Thanks Tom, I'll have a play with this later today.
-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] updating remote database

[Richard Huxton]

On Thursday 26 February 2004 02:54, Kenneth Gonsalves wrote:
> i have a database on a local machine, and another on a remote machine. I
> have a dialup connection between the two - so band width is a problem. What
> is the most efficient way of updating the remote from the local? Does SQL
> or postgres have simple commands for this?

Sounds like you want some form of (batched) asynchronous replication (as it's 
called). If you've not already set that up though, that won't help here.

Assuming you don't have a complete list of all changes logged somewhere, you 
might want to try:

1. pg_dump the tables you want to synchronise on the local machine (one per 
file)
2. Do the same on the remote machine
3. Use rsync to update the remote dump based on the local one
4. Restore the updated dump on the remote machine.

Failing that, you might want to look into the replication options available - 
you may be able to adapt contrib/dbmirror, or perhaps erserver/rservimp on 
gborg.postgresql.org

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] updating remote database

[Olivier Hubaut]

Kenneth Gonsalves wrote:

i have a database on a local machine, and another on a remote machine. I have 
a dialup connection between the two - so band width is a problem. What is the 
most efficient way of updating the remote from the local? Does SQL or 
postgres have simple commands for this?
Is the remote database a clone of the local one? In this case, rsync 
seems to me to be the faster method to do this, but you need to stop the 
two databases before doing this.

--
Signature en cours de maintenance,
Veuillez patienter...
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] updating remote database

[Achilleus Mantzios]

O kyrios Richard Huxton egrapse stis Feb 26, 2004 :

> On Thursday 26 February 2004 02:54, Kenneth Gonsalves wrote:
> > i have a database on a local machine, and another on a remote machine. I
> > have a dialup connection between the two - so band width is a problem. What
> > is the most efficient way of updating the remote from the local? Does SQL
> > or postgres have simple commands for this?
>

You said "update", which i assume is different from dump/restore.

I have made substantial modifications to dbmirror, which now provides
Foreign Key dependency oriented, row grained, conditional, Asynchronous, 
Lazy replication.

FK dependency oriented means that rows tables involved as parent in FK 
dependencies are replicated only if needed by a child row.

Row grained means the decision of mirroring depends on the
value of the actual row, thus giving the potential to
selectively mirror rows to certain slaves.

Conditional means that a table is either
- not mirrored at all
- unconditionally mirrored to all slaves (you can have many slaves)
- conditionally mirrored based on the value of column.

Async means you can have your updates in .sql text files
(in xaction order), and then transfer them (possibly bzip2'ed) and 
execute them to the client.
Also if you have tcp/ip you can call dbmirror in the traditional
way (online), leaving the job of compression to libpq.

Lazy means that you dont need to mirror anything in advance untill
its really needed.

Lets say you have a parent table, and you insert 1m rows.
Then a child table row that is to be mirrored is updated
to point to a row of the parent.
Then only this parent row will be mirrored to your client.
Now if a change is made on this very parent row, then
this change will be also mirrored to the client.

This way you will mirror only the minimal ammount of transactions
needed in order the 2 dbs to be in sync.

I warn you that i dont consider this work fully tested
(altho i think it will surely suit simple setups).
Also a planing phase is mandatory in order to have
the system running correctly.

There is an accounting mechanism that remembers who slave has
which row.


you can see some discussion here:
http://gborg.postgresql.org/pipermail/pgreplication-general/2003-December/001251.html

My version of dbmirror didnt make it to be an applied patch
mainly due to the different goals between the two.

However if you want the code or guidance just ask. 

> Sounds like you want some form of (batched) asynchronous replication (as it's 
> called). If you've not already set that up though, that won't help here.
> 
> Assuming you don't have a complete list of all changes logged somewhere, you 
> might want to try:
> 
> 1. pg_dump the tables you want to synchronise on the local machine (one per 
> file)
> 2. Do the same on the remote machine
> 3. Use rsync to update the remote dump based on the local one
> 4. Restore the updated dump on the remote machine.
> 
> Failing that, you might want to look into the replication options available - 
> you may be able to adapt contrib/dbmirror, or perhaps erserver/rservimp on 
> gborg.postgresql.org
> 
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Return more than a record

[Kumar]

Dear Friends,
 
Postgres 7.3.4 on RH Linux 7.2.
 
Get the following from the groupscreate or replace function ExpensiveDepartments() returns setof table1 as 
'
declare
r table1%rowtype;
begin
for r in select departmentid, sum(salary) as totalsalary
from GetEmployees() group by departmentid loop

if (r.totalsalary > 7) then
r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
else
r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;

if (r.totalsalary > 10) then
return next r.departmentid;
end if;

end loop;
return;
end
' 
language 'plpgsql';

Is possible for me to return a variable along with 
that 'return' statement? Because the table 'table1' contains some 
date
column. I have done some calculation on those 
columns and want to return the calculated  date along with that row of 
the
table1. How to do that. Please shed some 
light.

Re: [SQL] Field list from table

[Christopher Browne]

"Jan Pips" <[EMAIL PROTECTED]> wrote:
> How can I, using SELECT, get the full list of fields from a given table?

portfolio=# select column_name, data_type from information_schema.columns where 
table_catalog = 'portfolio' and table_schema = 'public' and table_name = 'stocktxns';
 column_name |data_type 
-+--
 symbol  | character varying
 date| timestamp with time zone
 price   | numeric
 quantity| numeric
(4 rows)

information_schema.columns has additional columns to provide
information about numeric precision, string widths, and such...
-- 
"cbbrowne","@","ntlug.org"
http://www.ntlug.org/~cbbrowne/postgresql.html
Rules  of the  Evil Overlord  #4.  "Shooting is  not too  good for  my
enemies." 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Return more than a record

[Kumar]

Dear Friends,
I am using the record type as follows in my code.

CREATE OR REPLACE FUNCTION fn_daily_calendar(date)
  RETURNS SETOF activities AS
DECLARE
 p_cal_date   ALIAS FOR $1;
 rec_activity   activities%ROWTYPE;
 v_activity_start_date   DATE;

BEGIN
 FOR rec_activity IN SELECT *   FROM activities  WHERE
DATE(activity_start_time) <= p_cal_date
 LOOP
  v_activity_start_date  := rec_activity.activity_start_time::DATE;
   IF  rec_activity.daily_gap   IS NOT NULL AND
rec_activity.recurrence_end_time  IS NULL THEN
 LOOP
  v_activity_start_date := v_activity_start_date +
rec_activity.daily_gap;
  IF v_activity_start_date = p_cal_date THEN
   RETURN next rec_activity;
  END IF;
  EXIT WHEN
   v_activity_start_date > p_cal_date + (1
month')::INTERVAL;
 END LOOP;
END IF;
 END LOOP;

 RETURN;
END;

See I am fetching the activity_start_time from the record, then assigning to
variable and do some calculations on the variable. Now I want to return the
value of v_activity_start_date for every row in activities table.

How could I achieve this.

Please shed some light.

Thanks
Kumar

- Original Message - 
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Thursday, February 26, 2004 8:59 PM
Subject: Re: [SQL] Return more than a record


> On Thu, 26 Feb 2004, Kumar wrote:
>
> > Get the following from the groups
> > create or replace function ExpensiveDepartments() returns setof table1
as
>
> Note that at least the example with this name in the SetReturningFunctions
> guide seems to use setof int as the return type.
>
> > '
> > declare
> > r table1%rowtype;
> > begin
> > for r in select departmentid, sum(salary) as totalsalary
> > from GetEmployees() group by departmentid loop
> >
> > if (r.totalsalary > 7) then
> > r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
> > else
> > r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
> > end if;
> >
> > if (r.totalsalary > 10) then
> > return next r.departmentid;
> > end if;
> >
> > end loop;
> > return;
> > end
> > '
> > language 'plpgsql';
> > Is possible for me to return a variable along with that 'return'
statement? Because the table 'table1' contains some date
> > column. I have done some calculation on those columns and want to return
the calculated  date along with that row of the
> > table1. How to do that. Please shed some light.
>
> If you want to return a composite type, you can make another rowtype
> variable that has the set of columns (and their types) to return, fill in
> the values to return and then do return next with that variable.
>
> For example, to say return departmentid, sum(salary) and the computed
> "totalsalary" from the above, you might do something like (untested so
> there might be syntactic errors)
>
> create type holder as (departmentid int, totalsalary int8);
> create type holder2 as (departmentid int, sumsalary int8, totalsalary
> int8);
>
> create or replace function ExpensiveDepartments() returns setof holder2 as
> '
> declare
> r holder%rowtype;
> s holder2%rowtype;
> begin
> for r in select departmentid, sum(salary) as totalsalary
> from GetEmployees() group by departmentid loop
>
> s.departmentid := r.departmentid;
> s.sumsalary := r.totalsalary;
>
> if (r.totalsalary > 7) then
> s.totalsalary := CAST(r.totalsalary * 1.75 as int8);
> else
> s.totalsalary := CAST(r.totalsalary * 1.5 as int8);
> end if;
>
> if (s.totalsalary > 10) then
> return next s;
> end if;
>
> end loop;
> return;
> end
> '
> language 'plpgsql';
>
>
> The important differences here are that we've got a new rowtype variable s
> of the return type and that we fill s with the values from r (the select)
> plus the calculation that we're doing (rather than before where we just
> overwrote the values in r.totalsalary) and then we return next s rather
> than a particular field.
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Return more than a record

[Stephan Szabo]

On Fri, 27 Feb 2004, Kumar wrote:

> Dear Friends,
> I am using the record type as follows in my code.
>
> CREATE OR REPLACE FUNCTION fn_daily_calendar(date)
>   RETURNS SETOF activities AS
> DECLARE
>  p_cal_date   ALIAS FOR $1;
>  rec_activity   activities%ROWTYPE;
>  v_activity_start_date   DATE;
>
> BEGIN
>  FOR rec_activity IN SELECT *   FROM activities  WHERE
> DATE(activity_start_time) <= p_cal_date
>  LOOP
>   v_activity_start_date  := rec_activity.activity_start_time::DATE;
>IF  rec_activity.daily_gap   IS NOT NULL AND
> rec_activity.recurrence_end_time  IS NULL THEN
>  LOOP
>   v_activity_start_date := v_activity_start_date +
> rec_activity.daily_gap;
>   IF v_activity_start_date = p_cal_date THEN
>RETURN next rec_activity;
>   END IF;
>   EXIT WHEN
>v_activity_start_date > p_cal_date + (1
> month')::INTERVAL;
>  END LOOP;
> END IF;
>  END LOOP;
>
>  RETURN;
> END;
>
> See I am fetching the activity_start_time from the record, then assigning to
> variable and do some calculations on the variable. Now I want to return the
> value of v_activity_start_date for every row in activities table.

One way would be: make a composite type that contains the columns of
activity + v_activity_start_date, make the function return that, add a
declared variable of that type, set the fields of that new variable to the
fields from rec_activity and the value of v_activity_start_date, return
next that variable.

---(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] Last day of month

[Michael Chaney]

On Thu, Feb 26, 2004 at 03:07:52AM -, Greg Sabino Mullane wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>  
>  
> > How to find the last sunday/mon/sat of any given month.
>  
> There is probably a smoother way to do it, but here is a
> quick little function to do what you ask. Feed it a date
> and a number, where 0 is Sunday, 1 is Monday, etc.

select date_trunc('month', current_date + '1 month'::interval) - '1 day'::interval +
(((3 - 7 - to_char(date_trunc('month', current_date + '1 month'::interval) -
'1 day'::interval,'D')::int) %7)||' days')::interval;

The "3" is the day of week (1 = Sunday, 7 = Saturday).  This equation
will return the date of the last "x" of the current month.  Change
"current_date" to be whatever date you wish to find the last "x" of.

Michael
--
Michael Darrin Chaney
[EMAIL PROTECTED]
http://www.michaelchaney.com/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] a few Questions about quoted varaibles in psql

[Leo Leo]

Hi!

How can I interpret a variable in psql, when the variable has to be quoted?

for example:

\set myVar myValue
\echo :myVar
The Result ist then "myValue" ==> ok

But: in my case the variable-values have to be in quotes:

\set db_username myUsername
\set db_password myPassword

CREATE USER :db_username WITH PASSWORD :db_password

==> This will not work, because you need quotes

CREATE USER :db_username WITH PASSWORD ':db_password'

==> This will create the user with Password :db_password and not myPassword
(so it takes the name of the varibale and not den value)

I played a bit with excaping, but even '\'':db_password'\'' will not work

What can I do, in order to achieve, that psql interprets a variable in
quotes?

Another Problem:

How can I concatenate a variable with a string without having a space in
between?

\set foo bar
\echo :foo test
==> bar test  (but there is a space in between)

\echo :foo||test
==> this does not work (I want bartest)
Is it somehow possible to do this?

Thanks for your help!

Leo

-- 
GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...)
jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Return more than a record

[Stephan Szabo]

On Thu, 26 Feb 2004, Kumar wrote:

> Get the following from the groups
> create or replace function ExpensiveDepartments() returns setof table1 as

Note that at least the example with this name in the SetReturningFunctions
guide seems to use setof int as the return type.

> '
> declare
> r table1%rowtype;
> begin
> for r in select departmentid, sum(salary) as totalsalary
> from GetEmployees() group by departmentid loop
>
> if (r.totalsalary > 7) then
> r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
> else
> r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
> end if;
>
> if (r.totalsalary > 10) then
> return next r.departmentid;
> end if;
>
> end loop;
> return;
> end
> '
> language 'plpgsql';
> Is possible for me to return a variable along with that 'return' statement? Because 
> the table 'table1' contains some date
> column. I have done some calculation on those columns and want to return the 
> calculated  date along with that row of the
> table1. How to do that. Please shed some light.

If you want to return a composite type, you can make another rowtype
variable that has the set of columns (and their types) to return, fill in
the values to return and then do return next with that variable.

For example, to say return departmentid, sum(salary) and the computed
"totalsalary" from the above, you might do something like (untested so
there might be syntactic errors)

create type holder as (departmentid int, totalsalary int8);
create type holder2 as (departmentid int, sumsalary int8, totalsalary
int8);

create or replace function ExpensiveDepartments() returns setof holder2 as
'
declare
r holder%rowtype;
s holder2%rowtype;
begin
for r in select departmentid, sum(salary) as totalsalary
from GetEmployees() group by departmentid loop

s.departmentid := r.departmentid;
s.sumsalary := r.totalsalary;

if (r.totalsalary > 7) then
s.totalsalary := CAST(r.totalsalary * 1.75 as int8);
else
s.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;

if (s.totalsalary > 10) then
return next s;
end if;

end loop;
return;
end
'
language 'plpgsql';


The important differences here are that we've got a new rowtype variable s
of the return type and that we fill s with the values from r (the select)
plus the calculation that we're doing (rather than before where we just
overwrote the values in r.totalsalary) and then we return next s rather
than a particular field.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Scalar in a range (but textual not numeric)

[Bruno Wolff III]

On Thu, Feb 26, 2004 at 07:55:14 -0700,
  Edmund Bacon <[EMAIL PROTECTED]> wrote:
> On Wed, 2004-02-25 at 12:18, Richard Huxton wrote:
> 
> As a complete aside:
> 
> Is there any advantage to use varchar instead of type text?

Only if there is a business rule that limits the length of the data.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Scalar in a range (but textual not numeric)

[Edmund Bacon]

On Wed, 2004-02-25 at 12:18, Richard Huxton wrote:

As a complete aside:

Is there any advantage to use varchar instead of type text?

Thanks
  



---(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