Thread: Question about SQL FUnction

hi,

i have some tables:

create table person (nr int4, name varchar(50));
create table book(nr int4, title varchar(50), persnr int4);

(persnr is the foreign key -> person.nr)

now i have some
CREATE FUNCTION get_new_nr(**TABLE**,**FIELDS**) RETURNS int4;
   AS 'SELECT MAX($2) FROM $1 +1;'
   LANGUAGE 'sql';

but **table** and **fields** needs some datatype. this function should
return the max of a set of all numbers in person (+1) and return this.

is it possible (and how ;-) to write such a very _flexible_ function??

MArtin

On Wed, 17 Feb 1999, Martin [iso-8859-1] M�derndorfer wrote:

> hi,

Hi Martin

>
> i have some tables:
>
> create table person (nr int4, name varchar(50));
> create table book(nr int4, title varchar(50), persnr int4);
>
> (persnr is the foreign key -> person.nr)
>
> now i have some
> CREATE FUNCTION get_new_nr(**TABLE**,**FIELDS**) RETURNS int4;
>    AS 'SELECT MAX($2) FROM $1 +1;'
>    LANGUAGE 'sql';
>
> but **table** and **fields** needs some datatype. this function should
> return the max of a set of all numbers in person (+1) and return this.

Are you trying to get the MAX($2) in order to calculate the next value for
the primaryy key ????

If that's true, i would recommend you to use sequences

>
> is it possible (and how ;-) to write such a very _flexible_ function??
>
> MArtin
>

Functions written in sql are not very flexible... you just can't use the
arguments very well. It is better to write C functions with libpq.

Adios amigo.


David Martinez Cuevas
     Office 622-60-80      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     Home 565-25-17          "Eat Linux, Drink Linux...  SMOKE LINUX "
                           @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

>>>>> "d" == derndorfer  <Martin> writes:

 d> hi,
 d> i have some tables:

 d> create table person (nr int4, name varchar(50));
 d> create table book(nr int4, title varchar(50), persnr int4);

 d> (persnr is the foreign key -> person.nr)

 d> now i have some
 d> CREATE FUNCTION get_new_nr(**TABLE**,**FIELDS**) RETURNS int4;
 d> AS 'SELECT MAX($2) FROM $1 +1;'
 d> LANGUAGE 'sql';

 d> but **table** and **fields** needs some datatype. this function should
 d> return the max of a set of all numbers in person (+1) and return this.

 d> is it possible (and how ;-) to write such a very _flexible_ function??

 d> MArtin

I think this is not possible in 'sql' and 'plpgsql' functions. You
must use Tcl or C languages for these function. But if you wont have
auto-increment unique field you can use sequences:

create sequence s_person;

create table person (
  nr int default nextval('s_person') not null,
  name varchar(50)
);

insert into person (name) values('itisme');
insert into person (name) values('andyou');

select * from person;
nr    name
1     itisme
2     andyou


--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer