Thread: Why the weak key is created as unique

Example:

mydb=> create table AAA (a serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence 'aaa_a_seq' for SERIAL column 'aaa.a'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'aaa_pkey' for table 'aaa'
CREATE

mydb=> create table BBB (a serial references AAA, b integer, primary key(a,b));
NOTICE:  CREATE TABLE will create implicit sequence 'bbb_a_seq' for SERIAL column 'bbb.a'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'bbb_pkey' for table 'bbb'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'bbb_a_key' for table 'bbb'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

mydb=> insert into AAA values (1);
INSERT 20369 1

mydb=> insert into BBB values (1,1);
INSERT 20370 1
mydb=> insert into BBB values (1,2);
ERROR:  Cannot insert a duplicate key into unique index bbb_a_key

I would like that the pair keys (a,b) was unique.
Certainly, I can remove unique index 'bbb_a_key'...
But how more correctly?

--
Marat Khairullin mailto:xmm@rambler.ru
Marat.Khairullin@f92.n5049.z2.fidonet.org
----
  Бесплатная почта http://mail.Rambler.ru/
  Рамблер-Покупки http://ad.rambler.ru/ban.clk?pg=1691&bn=9346

You probably do not want a serial in BBB since you want
to be setting the values.  Use "a int references AAA" instead
I think.

Hi,
I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
work in queries executed from Pl/pgSQl. Here is an example:

create procedure get_name(varchar)
...
BEGIN   query := ''SELECT first_name || '''' '''' || last_name FROM user'';   EXECUTE query;
...
END;
...

Basically I want to get full name, i.e. first name separated with space from
the last name. If I follow the PL/pgSQL manual as shown above, I get parse
error.
What am I doing wrong?
thanks,

Oleg

Oleg,

I'm assuming that this is just a hypothetical example, as the below is
far from the fastest way to get something as simple as a name.

> BEGIN
>     query := ''SELECT first_name || '''' '''' || last_name FROM
> user'';
>     EXECUTE query;
> ...
> END;
> ...

I'm pretty sure your quotes are correct.  However, I believe "query" is
a reserved word.  Try using a different variable name.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On Fri, 5 Oct 2001, Oleg Lebedev wrote:

> Hi,
> I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
> work in queries executed from Pl/pgSQl. Here is an example:
> 
> create procedure get_name(varchar)
> ...
> BEGIN
>     query := ''SELECT first_name || '''' '''' || last_name FROM user'';
>     EXECUTE query;
> ...
> END;
> ...
> 
> Basically I want to get full name, i.e. first name separated with space from
> the last name. If I follow the PL/pgSQL manual as shown above, I get parse
> error.
> What am I doing wrong?

Odd, a script like the following works for me on both 7.1 and 7.2devel:

drop table aa;
drop function fgn(varchar);
create table aa(a varchar);
insert into aa values ('d');
create function fgn(varchar) returns text as '
DECLAREquery text;rec record;
BEGINquery := ''SELECT a || '''' '''' || a as bar from aa;'';RAISE NOTICE ''%'', query;FOR rec in EXECUTE query LOOP
returnrec.bar;END LOOP;
 
END;'
language 'plpgsql';
select fgn('f');

I just upgraded to 7.1 and the query works for me now.
thanks,

Oleg

Stephan Szabo wrote:

> On Fri, 5 Oct 2001, Oleg Lebedev wrote:
>
> > Hi,
> > I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
> > work in queries executed from Pl/pgSQl. Here is an example:
> >
> > create procedure get_name(varchar)
> > ...
> > BEGIN
> >     query := ''SELECT first_name || '''' '''' || last_name FROM user'';
> >     EXECUTE query;
> > ...
> > END;
> > ...
> >
> > Basically I want to get full name, i.e. first name separated with space from
> > the last name. If I follow the PL/pgSQL manual as shown above, I get parse
> > error.
> > What am I doing wrong?
>
> Odd, a script like the following works for me on both 7.1 and 7.2devel:
>
> drop table aa;
> drop function fgn(varchar);
> create table aa(a varchar);
> insert into aa values ('d');
> create function fgn(varchar) returns text as '
> DECLARE
>  query text;
>  rec record;
> BEGIN
>  query := ''SELECT a || '''' '''' || a as bar from aa;'';
>  RAISE NOTICE ''%'', query;
>  FOR rec in EXECUTE query LOOP
>   return rec.bar;
>  END LOOP;
> END;'
> language 'plpgsql';
> select fgn('f');