Thread: question on passing parameter in sql query

Hi,

   I don't want to compare with Oracle and postgres. But I have a situation.
  I am using psql command line tool supplied by postgres.

   In Oracle I can say

    select * from emp where emp_id = &1

   Oracle will ask:
   Enter a value for 1:

   If I enter 10, then Oracle will get the empid=10

  What is the equal command in postgres ?

Regards
skarthi

_________________________________________________________________
Invite your Hotmail contacts to join your friends list with Windows Live
Spaces

http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

On 2/7/07, Karthikeyan Sundaram <skarthi98@hotmail.com> wrote:
It can be done, but it is a bit "different" and this method is UNIX dependent:

test=# create table data (x integer not null primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "data_pkey" for table "data"
CREATE TABLE
test=# insert into data values (1),(2),(3),(4);
INSERT 0 4
test=# \set foo `head -1`
3
test=# \echo :foo
3
test=# select * from data where x = :foo;
 x
---
 3
(1 row)


I don't believe it automatically prompts the way Oracle does.  It would be nice if there was a built-in "\prompt [VARIABLE] [TEXT]".


--
Chad
http://www.postgresqlforums.com/

Hi,

    We are using Postgres 8.1.0

  Question No 1:
  =========
   There are lots of system tables that are available in postgres. For
example pg_tables will have all the information about the tables that are
present in a given schema.  pg_views will have all the information about the
views for the given schema.

    I want to find all the sequences.  What is the system tables that have
the information about all the sequences?

   Question No 2:
   =========

     I have 2 postgres instance located in two different servers.   I want
to create a DBlink (like in Oracle) between these 2.  What are the steps
involved to create this.

   Any examples?  Please advise.

Regards
skarthi

_________________________________________________________________
Win a Zune��make MSN� your homepage for your chance to win!
http://homepage.msn.com/zune?icid=hmetagline

>     I want to find all the sequences.  What is the system tables that have
> the information about all the sequences?

psql -E -U <user> <db>
\ds

Capture the query that psql sends to the server.

Can't help with the dblink -- sorry.

-- Gary Chambers

// Nothing fancy and nothing Microsoft!

On Wed, 2007-02-28 at 12:19, Karthikeyan Sundaram wrote:
> Hi,
>
>     We are using Postgres 8.1.0

Stop.  Do not pass go, do not collect $200.  Update your postgresql
installation now to 8.1.8.  There were a lot of bugs fixed between 8.1.0
and 8.1.8.

After that...

>   Question No 1:
>   =========
>    There are lots of system tables that are available in postgres. For
> example pg_tables will have all the information about the tables that are
> present in a given schema.  pg_views will have all the information about the
> views for the given schema.
>
>     I want to find all the sequences.  What is the system tables that have
> the information about all the sequences?

In the future, you can use this trick to find those things out:

psql -E template1
\?   (command to list all the backslash commands from psql)
\ds  (<- command for listing sequences from psql)
Tada, you now get the sql that psql used to make that display.

For 8.2.3 that's:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

>    Question No 2:
>    =========
>
>      I have 2 postgres instance located in two different servers.   I want
> to create a DBlink (like in Oracle) between these 2.  What are the steps
> involved to create this.
>
>    Any examples?  Please advise.

I'm pretty sure there's some examples in the contrib/dblink/doc
directory in the source file to do that.  It's pretty simple, I had it
working about 5 minutes after installing dblink.

Hi,

     I am using 8.2.1 on my dev server.

   When I do a pg_dump, I am getting an error message.

pg_dump -U postgres  podcast -t channel

pg_dump: symbol lookup error: pg_dump: undefined symbol: PQescapeStringConn

   How can I resolved this? What may be the problem?

    Because of this, I am not able to dump anything.


Regards
skarthi

_________________________________________________________________
Play Flexicon: the crossword game that feeds your brain. PLAY now for FREE.�
  http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline

Hi Everybody,

    I have 2 versions of postgres 8.1.0 is my production version and 8.2.1
is my development version.

  I am trying to create a view in my development version (8.2.3)

   create view chnl_vw as select * from channel with check option;

   I am getting an error message:

[Error] Script lines: 1-1 --------------------------
ERROR: WITH CHECK OPTION is not implemented
Line: 1

   what does this mean?  I looked at the 8.2.1 manual and found the create
view has check option.  But it says before 8.2 those options are
unsupported.

   How can I make this command to work.

Regards
skarthi

_________________________________________________________________
5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free
quotes - *Terms

https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910

"Karthikeyan Sundaram" <skarthi98@hotmail.com> writes:
>    I am getting an error message:
> ERROR: WITH CHECK OPTION is not implemented

>    what does this mean?

It seems perfectly clear to me ...

            regards, tom lane

"hubert depesz lubaczewski" <depesz@gmail.com> writes:
> On 3/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> ERROR: WITH CHECK OPTION is not implemented
>> It seems perfectly clear to me ...

> errors is clear, but maybe the information about check option should
> be removed from docs to 8.2?

There is no place in the 8.2 docs that claims it is implemented.

            regards, tom lane

Hi everybody,

   I have implemented in a different way as advised in the manual.  I
thought this will be useful for everbody.

   We don't have check option in the view.  Instead we can create a rule to
make the view as insertable, updatable or delete.

  Here is the script.

create table test_tbl (a int4, b int4, c varchar(30));

create or replace view test_vw as select * from test_tbl;

create or replace rule test_rule_ins as on insert to test_vw
do instead
insert into test_tbl values (new.a, new.b, new.c);

insert into test_vw (a, b) values (1,2);
insert into test_vw (a, b) values (3,4);

create or replace rule test_rule_upd as on update to test_vw
do instead
update test_tbl set a=new.a, b=new.b, c=new.c where a=new.a;


update test_vw set c='good' where a=1;

select * from test_vw;


regards
skarthi

>From: "Karthikeyan Sundaram" <skarthi98@hotmail.com>
>To: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
>Subject: [ADMIN] create view with check option
>Date: Sun, 18 Mar 2007 22:38:05 -0700
>
>Hi Everybody,
>
>    I have 2 versions of postgres 8.1.0 is my production version and 8.2.1
>is my development version.
>
>  I am trying to create a view in my development version (8.2.3)
>
>   create view chnl_vw as select * from channel with check option;
>
>   I am getting an error message:
>
>[Error] Script lines: 1-1 --------------------------
>ERROR: WITH CHECK OPTION is not implemented
>Line: 1
>
>   what does this mean?  I looked at the 8.2.1 manual and found the create
>view has check option.  But it says before 8.2 those options are
>unsupported.
>
>   How can I make this command to work.
>
>Regards
>skarthi
>
>_________________________________________________________________
>5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free
>quotes - *Terms

>https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

_________________________________________________________________
i'm making a difference.�Make every IM count for the cause of your choice.
Join Now.

http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=hmtagline