Обсуждение: 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

Karthikeyan Sundaram wrote:
> 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.

Sounds like you have two different versions of pg_dump on your box. Did
you recently try to upgrade?

Sincerely,

Joshua D. Drake


>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Hi Joshua,

   Thanks for your reply.  No, I recently installed (fresh installation)
from scratch.

Regards
skarthi



>From: "Joshua D. Drake" <jd@commandprompt.com>
>To: Karthikeyan Sundaram <skarthi98@hotmail.com>
>CC: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
>Subject: Re: [ADMIN] pg_dump error
>Date: Wed, 28 Feb 2007 11:31:01 -0800
>
>Karthikeyan Sundaram wrote:
> > 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.
>
>Sounds like you have two different versions of pg_dump on your box. Did
>you recently try to upgrade?
>
>Sincerely,
>
>Joshua D. Drake
>
>
> >
> >
> > 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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >               http://archives.postgresql.org
> >
>
>
>--
>
>       === The PostgreSQL Company: Command Prompt, Inc. ===
>Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>Providing the most comprehensive  PostgreSQL solutions since 1997
>              http://www.commandprompt.com/
>
>Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate

_________________________________________________________________
Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
Intro*Terms

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

Hi,

   I have to dump only 10 tables out of 100 tables.  In the pg_dump utility
given by postgres there is an option called -t followed by table name.

    In that option, if I give more than 1 table, it's not accepting.

    How can I get the dump in one stroke for all the 10 tables? Please
advise.

Regards
skarthi

_________________________________________________________________
Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
Intro*Terms

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

Karthikeyan Sundaram wrote:
> Hi,
>
>    I have to dump only 10 tables out of 100 tables.  In the pg_dump utility
> given by postgres there is an option called -t followed by table name.
>
>     In that option, if I give more than 1 table, it's not accepting.
>
>     How can I get the dump in one stroke for all the 10 tables? Please
> advise.
>
> Regards
> skarthi

If you are using postgresql 8.2, you can specify multiple tables
http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html

Don't think that that was possible before 8.2, though.

"Karthikeyan Sundaram" <skarthi98@hotmail.com> writes:
>    Thanks for your reply.  No, I recently installed (fresh installation)
> from scratch.

Well, your pg_dump seems to be finding an older version of libpq.so from
somewhere.  Check for a pre-existing postgresql package.

            regards, tom lane

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

On 3/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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 ...

errors is clear, but maybe the information about check option should
be removed from docs to 8.2? it is still not there in latest builds of
8.3devel.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

"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

Karthikeyan Sundaram wrote:
> 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.

Why are you trying to declare a view "with check option" using a 8.2 engine?
Can you show us the part of manual that say you can use that syntax?

I see:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]   AS query


Regards
Gaetano Mendola

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