Thread: how to store a query, that results in a table

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.

Hi, there, <p>If the client machine is a trusted machine in your company, use remote shell 'rsh' can <br />call a
scriptof SQL. <br />If not , you had better use embeded SQL. <p>I don't know how visual basic embed SQL, but I think is
sameway as other language, <br />in postgres: <br />#db> create  user robot with password 'wowwow'; <br />$db>
grantall on table1 to robot; <p>In perl , it's very similar like this: <br />use Pg; <br />my $conn; <br />        sub
connectDb{ <br />           $conn = Pg::connectdb("dbname=db host=dbserver port=5432 user=robot password=wowwow"); <br
/>          if ( $conn->status != Pg::PGRES_CONNECTION_OK ) { <br />                die "Cant open postgres! : " .
$conn->errorMessage. "\n"; <br />                } <br />           return; <br />         } <br />#main <br
/>       connectDb(); <br />        my $query="select * from table1 where row1=parameter1"; <br />        print
$query,"\n";<br />        my $res=$conn->exec($query); <br />        $res->cmdStatus || die
$conn->errorMessage.":$!\n";<br />        my $rows= $res->ntuples; <br />        for (my $k = 0 ; $k < $rows;
$k++){<br />                my $field1=$res->getvalue($k,0); <br />                my
$field2=$res->getvalue($k,1);<br />                print $field1,"\t",field2,"\n"; <br />        } <br />  <p>root
wrote:<blockquote type="CITE">hi. <br />I have a problem. <br />I require to store a query sql in postgresql-7.0 like:
<br/>example. <br />select * from table1 where row1(table1)=parameter1 <br />If i execute this query directly, I don't
haveproblem. <br />I want to store this query in order to execute from a client program <br />(visual basic 6.0), but i
don'tknow how to? <br />I tried to store the query like a function (create function ...), but it <br />was impossible
tofind a way in order to obtain a table like result. <br />Only we could obtain a single row by using the rtype "
returnssetof <br />varchar". <br />I hope a soon answer. <br />Thank you. <br /> Nelson B.</blockquote><pre>-- 
 
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</pre>  

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.

Hi, <br />So, your question is not client side, <br />you want store a procedure in db(server side) can accept para
fromclient side. <br />I have not seen Pg has a functionality to do like store procedure or package like Oracle <br />(
maybeI don't know), so , I suggest that : <br />1 use embeded SQL <br />2. store query as a SQL in shell script(
becauseshell script can accept parameters) <br />    in shell , you can say: <br />    echo "select * from table1 where
row1=$1"|rshpg_server /usr/local/pgsql/bin/psql -U robt db <br />    (e.g . remote shell call, local is same) <br />3.
ifyou use bash shell, you can also use pgbash(<a
href="http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html)">http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html)</a><p>Nelson
wrote:<blockquote type="CITE">thank you jie Liang for your response, but my problems are: <br />1. How to store a query
inthe database. <br />2. How to give a parameter from outside of database, for example: <br />select * from table1
whererow1 = my_parameter_outside. <br />Give me an example please.</blockquote><pre>-- 
 
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</pre>  

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

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

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