Re: [SQL] Obtaining the primary key of the record just inserted

2007-11-08 Thread Scott Marlowe 
On Nov 8, 2007 11:56 AM, Oliveiros Cristina
<[EMAIL PROTECTED]> wrote:
>
> Hello, All,
>
> I have the need to know the primary key assigned to a record I've just
> INSERTed .
>
> Is there an easy way to solve this ?
>
> Similar to SQLServer's  SELECT scope_identity() ; ?

In 8.2 and up:

insert into table a (info) values ('abc') returning id;

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Partitioning and Foreign Keys

2007-11-08 Thread Vivek Khera 


On Nov 4, 2007, at 12:57 PM, Volkan YAZICI wrote:


major bottleneck. I planned to partition this table, but I learnt that
PostgreSQL doesn't allow referencing views. Does anybody have any
suggestions?



I can envision writing your own custom trigger instead of using the  
stock FK trigger that knows which sub-table to check for the FK  
reference.   Then add that as an insert/update trigger on your  
referencing table.


I will be investigating this approach in the next few months; right  
now I don't have need for this particular case.



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

[SQL] Obtaining the primary key of the record just inserted

2007-11-08 Thread Oliveiros Cristina 
 
Hello, All,

I have the need to know the primary key assigned to a record I've just INSERTed 
. 

Is there an easy way to solve this ?

Similar to SQLServer's  SELECT scope_identity() ; ?

Any help deeply appreciated

Best,
Oliveiros

[SQL] quote_nullable alternative?

2007-11-08 Thread Philippe Lang 
Hi,

There has been a recent discussion on the hacker mailing-list recently,
regarding a new patch for postgresql, that returns 'NULL' instead of
NULL when a NULL value is passed as a parameter.

It's exactly what I need on my 8.2.5 server.

Is there an alternative to patching the server in order to get that kind
of feature, for example with plpgsql code?

Because of that bug:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

... I'm obliged to call EXECUTE at every temporary table call, and
constructing the query string where parameters can be NULL is really a
nightmare...

Thanks

Philippe Lang

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Obtaining the primary key of the record just inserted

2007-11-08 Thread Oliveiros Cristina 

It worked perfectly.
Thanks a million, Scott.

Best,
Oliveiros
- Original Message - 
From: "Scott Marlowe" <[EMAIL PROTECTED]>

To: "Oliveiros Cristina" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, November 08, 2007 6:03 PM
Subject: Re: [SQL] Obtaining the primary key of the record just inserted



On Nov 8, 2007 11:56 AM, Oliveiros Cristina
<[EMAIL PROTECTED]> wrote:


Hello, All,

I have the need to know the primary key assigned to a record I've just
INSERTed .

Is there an easy way to solve this ?

Similar to SQLServer's  SELECT scope_identity() ; ?


In 8.2 and up:

insert into table a (info) values ('abc') returning id;



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] quote_nullable alternative?

2007-11-08 Thread Philippe Lang 
[EMAIL PROTECTED] wrote:
> Hi,
> 
> There has been a recent discussion on the hacker mailing-list
> recently, regarding a new patch for postgresql, that returns 'NULL'
> instead of NULL when a NULL value is passed as a parameter.  
> 
> It's exactly what I need on my 8.2.5 server.
> 
> Is there an alternative to patching the server in order to get that
> kind of feature, for example with plpgsql code? 
> 
> Because of that bug:
> 
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
> 
> ... I'm obliged to call EXECUTE at every temporary table call, and
> constructing the query string where parameters can be NULL is really
> a nightmare...  
> 
> Thanks

Hi,

It might not be the most elegant solution, but I ended up writing these
functions to solve my problem:


CREATE OR REPLACE FUNCTION nullable_param(param text)
  RETURNS text AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

  CREATE OR REPLACE FUNCTION nullable_param(param varchar)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param decimal)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN CAST(param AS varchar);
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param int)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN CAST(param AS varchar);
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param date)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;



Regards,

Philippe

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org