pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how to store a query, that results in a table
7+ messages / 6 participants
[nested] [flat]

* how to store a query, that results in a table
@ 2000-09-22 13:58 root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
  0 siblings, 1 reply; 7+ messages in thread

From: root @ 2000-09-22 13:58 UTC (permalink / raw)
  To: pgsql-sql-owner@hub.org; pgsql-sql@hub.org; pgsql-general@postgresql.org; pgsql-sql; pgsql-general@hub.org

hi.
I have a problem.
I require to store a query sql in postgresql-7.0 like:
example.
select * from table1 where row1(table1)=parameter1
If i execute this query directly, I don't have problem.
I want to store this query in order to execute from a client program
(visual basic 6.0), but i don't know how to?
I tried to store the query like a function (create function ...), but it
was impossible to find a way in order to obtain a table like result.
Only we could obtain a single row by using the rtype " returns setof
varchar".
I hope a soon answer.
Thank you.
 Nelson B.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
@ 2000-09-22 21:41 ` Jie Liang <jliang@ipinc.com>
  2000-09-22 22:09   ` [SQL] how to store a query, that results in a table Nelson <nbrito@cmet.net>
  2000-09-23 01:14   ` Re: how to store a query, that results in a table Keith Wong <keith@e-magine.com.au>
  0 siblings, 2 replies; 7+ messages in thread

From: Jie Liang @ 2000-09-22 21:41 UTC (permalink / raw)
  To: nbrito@cmet.net; +Cc: pgsql-sql-owner@hub.org; pgsql-sql@hub.org; pgsql-general@postgresql.org; pgsql-sql; pgsql-general@hub.org

Hi, there,

If the client machine is a trusted machine in your company, use remote
shell 'rsh' can
call a script of SQL.
If not , you had better use embeded SQL.

I don't know how visual basic embed SQL, but I think is same way as other
language,
in postgres:
#db> create  user robot with password 'wowwow';
$db> grant all on table1 to robot;

In perl , it's very similar like this:
use Pg;
my $conn;
        sub connectDb {
           $conn = Pg::connectdb("dbname=db host=dbserver port=5432
user=robot password=wowwow");
           if ( $conn->status != Pg::PGRES_CONNECTION_OK ) {
                die "Cant open postgres! : " . $conn->errorMessage . "\n";
                }
           return;
         }
#main
        connectDb();
        my $query="select * from table1 where row1=parameter1";
        print $query,"\n";
        my $res=$conn->exec($query);
        $res->cmdStatus || die $conn->errorMessage.":$!\n";
        my $rows= $res->ntuples;
        for (my $k = 0 ; $k < $rows; $k++){
                my $field1=$res->getvalue($k,0);
                my $field2=$res->getvalue($k,1);
                print $field1,"\t",field2,"\n";
        }


root wrote:

> hi.
> I have a problem.
> I require to store a query sql in postgresql-7.0 like:
> example.
> select * from table1 where row1(table1)=parameter1
> If i execute this query directly, I don't have problem.
> I want to store this query in order to execute from a client program
> (visual basic 6.0), but i don't know how to?
> I tried to store the query like a function (create function ...), but it
> was impossible to find a way in order to obtain a table like result.
> Only we could obtain a single row by using the rtype " returns setof
> varchar".
> I hope a soon answer.
> Thank you.
>  Nelson B.

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [SQL] how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
@ 2000-09-22 22:09   ` Nelson <nbrito@cmet.net>
  2000-09-22 22:26     ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Nelson @ 2000-09-22 22:09 UTC (permalink / raw)
  To: Jie Liang <jliang@ipinc.com>; pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-novice@hub.org <pgsql-novice@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

thank you jie Liang for your response, but my problems are:
1. How to store a query in the database.
2. How to give a parameter from outside of database, for example:
select * from table1 where row1 = my_parameter_outside.
Give me an example please.

Attachments:

nbrito.vcf (text/x-vcard, 149B) download begin:vcard n:brito;nelson x-mozilla-html:FALSE adr:;;;;;; version:2.1 email;internet:nbrito@cmet.net x-mozilla-cpt:;28480 fn:nelson brito end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
  2000-09-22 22:09   ` [SQL] how to store a query, that results in a table Nelson <nbrito@cmet.net>
@ 2000-09-22 22:26     ` Jie Liang <jliang@ipinc.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Jie Liang @ 2000-09-22 22:26 UTC (permalink / raw)
  To: Nelson <nbrito@cmet.net>; +Cc: pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-novice@hub.org <pgsql-novice@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

Hi,
So, your question is not client side,
you want store a procedure in db(server side) can accept para from
client side.
I have not seen Pg has a functionality to do like store procedure or
package like Oracle
( maybe I don't know), so , I suggest that :
1 use embeded SQL
2. store query as a SQL in shell script( because shell script can accept
parameters)
    in shell , you can say:
    echo "select * from table1 where row1=$1"|rsh pg_server
/usr/local/pgsql/bin/psql -U robt db
    (e.g . remote shell call, local is same)
3. if you use bash shell, you can also use
pgbash(http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html)

Nelson wrote:

> thank you jie Liang for your response, but my problems are:
> 1. How to store a query in the database.
> 2. How to give a parameter from outside of database, for example:
> select * from table1 where row1 = my_parameter_outside.
> Give me an example please.

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
@ 2000-09-23 01:14   ` Keith Wong <keith@e-magine.com.au>
  2000-09-23 14:08     ` Re: [SQL] how to store a query, that results in a table Paul Wehr <paul@industrialsoftworks.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Keith Wong @ 2000-09-23 01:14 UTC (permalink / raw)
  To: Nelson <nbrito@cmet.net>; Jie Liang <jliang@ipinc.com>; pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-novice@hub.org <pgsql-novice@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

This is not really possible with postgresql at the moment.
Better off trying to work around, perhaps using a view. That way you have a 
way to change the select
statement without actually modifying your client code.

Keith.

At 06:09 PM 22/09/2000 -0400, Nelson wrote:
>thank you jie Liang for your response, but my problems are:
>1. How to store a query in the database.
>2. How to give a parameter from outside of database, for example:
>select * from table1 where row1 = my_parameter_outside.
>Give me an example please.
>
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
  2000-09-23 01:14   ` Re: how to store a query, that results in a table Keith Wong <keith@e-magine.com.au>
@ 2000-09-23 14:08     ` Paul Wehr <paul@industrialsoftworks.com>
  2000-09-23 16:20       ` Re: [SQL] how to store a query, that results in a table Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Paul Wehr @ 2000-09-23 14:08 UTC (permalink / raw)
  To: Keith Wong <keith@e-magine.com.au>; +Cc: Nelson <nbrito@cmet.net>; Jie Liang <jliang@ipinc.com>; pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-novice@hub.org <pgsql-novice@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

Summary:  Proposed solution, and question on efficiency of technique

I don't know if this is what you are looking for, but I have a database where
I needed a relatively complex view (which I can do thanks to the expanded view
buffer in 7.0!, it didn't fit in 6.5.3), but I need to pass an "effective
date" to the view that needed to be in a range (so I couldn't just use a
column in one of the source tables) to get the results I want.  My "solution"
was to come up with an "effective dates" table with one column (primary keyed)
that I can put the dates in.  For example:

create table effective_date (date date, primary key (date) );

create view complex_view as select blah, ..... , effective_date.date
from tablea, tableb, tablec, effective_date
where tablea.foo=tableb.foo
   ....
  and effective_date.date between tablec.start_date=tablec.end_date
;

then, when I want to select rows from the view, I have to INSERT the date I
want into the "effective_date" table (which is effectively my "parameter"),
then I can select it from the view.  i.e.:

insert into effective_date values ('09/23/2000');  -- may "fail" if date is
already in the table, but if it is, who cares?

select *
from complex_view
where date='09/23/2000';

Now it would certainly be nicer if I could set some kind of global system
variable to the date, then reference that system variable in the view, but I
couldn't figure out any way to do it.  If anyone in the know is screaming out
loud at this technique, please point me in the right direction, I would love
to be able to skip the "Insert into effective_date..." step.

-paul



Keith Wong wrote:

> This is not really possible with postgresql at the moment.
> Better off trying to work around, perhaps using a view. That way you have a
> way to change the select
> statement without actually modifying your client code.
>
> Keith.
>
> At 06:09 PM 22/09/2000 -0400, Nelson wrote:
> >thank you jie Liang for your response, but my problems are:
> >1. How to store a query in the database.
> >2. How to give a parameter from outside of database, for example:
> >select * from table1 where row1 = my_parameter_outside.
> >Give me an example please.
> >
> >




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] how to store a query, that results in a table
  2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
  2000-09-22 21:41 ` Re: how to store a query, that results in a table Jie Liang <jliang@ipinc.com>
  2000-09-23 01:14   ` Re: how to store a query, that results in a table Keith Wong <keith@e-magine.com.au>
  2000-09-23 14:08     ` Re: [SQL] how to store a query, that results in a table Paul Wehr <paul@industrialsoftworks.com>
@ 2000-09-23 16:20       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2000-09-23 16:20 UTC (permalink / raw)
  To: Paul Wehr <paul@industrialsoftworks.com>; +Cc: Keith Wong <keith@e-magine.com.au>; Nelson <nbrito@cmet.net>; Jie Liang <jliang@ipinc.com>; pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-novice@hub.org <pgsql-novice@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

Paul Wehr <paul@industrialsoftworks.com> writes:
> create view complex_view as select blah, ..... , effective_date.date
> from tablea, tableb, tablec, effective_date
> where tablea.foo=tableb.foo
>    ....
>   and effective_date.date between tablec.start_date=tablec.end_date
> ;

> insert into effective_date values ('09/23/2000');  -- may "fail" if date is
> already in the table, but if it is, who cares?

> select *
> from complex_view
> where date='09/23/2000';

Er, why don't you just do

select * from complex_view
where '09/23/2000' between start_date and end_date;

I don't see what the effective_date table is buying ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-09-23 16:20 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-22 13:58 how to store a query, that results in a table root <nbrito@cmet.net>
2000-09-22 21:41 ` Jie Liang <jliang@ipinc.com>
2000-09-22 22:09   ` Nelson <nbrito@cmet.net>
2000-09-22 22:26     ` Jie Liang <jliang@ipinc.com>
2000-09-23 01:14   ` Keith Wong <keith@e-magine.com.au>
2000-09-23 14:08     ` Paul Wehr <paul@industrialsoftworks.com>
2000-09-23 16:20       ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox