Thread: [SQL] CREATE TABLE (fwd ): again!

---------- Forwarded message ----------
Date: Fri, 5 Mar 1999 15:06:28 -0500 (GMT+5)
From: Ingrith Andrea Correa Vargas <i-correa@uniandes.edu.co>
Subject: [SQL] CREATE TABLE (fwd ): again!


Hello all!

I beleive that somebody knows how to answer my question...please!!


Make me a favor, please: Im going to send to you what I did, can you see
that and tell me what could be bad?!.

The example is:


---------------------------------------------------------
CREATE TABLE emp (code INT4
  , name VARCHAR(20)
  , salary INT4
  , 2salary INT4 default double_salary(emp)
);

CREATE FUNCTION double_salary(emp) RETURNS INT4
AS 'SELECT $1.salay * 2 AS result' LANGUAGE 'sql'
;

ERROR:  parser: syntax error at or near ")"
---------------------------------------------------------

Function double_salary receives an 'emp' , as you see, and there is the
error, because if I change parameter 'emp' by a number, the error doesn't
appear.

How can I do?!

The origin of this question is that I want to know if a subclass inherits
all atributtes from the parent, included functions like double_salary in
my example.

Can you help me?!...


Thank you in advance.


// Ingrith :)

> Hello all!
>
> I beleive that somebody knows how to answer my question...please!!

I think the reason you are not getting an (immediate) answer from the
list is that you appear to be exploring the stuff that has been pretty
much abandoned since postgres95, not because we are insensitive barbarians
with disrespect for newbies ;)

There are still some areas in postgres about which people just can't
speak with confidence.

> Make me a favor, please: Im going to send to you what I did, can you see
> that and tell me what could be bad?!.
>
> The example is:
>
>
> ---------------------------------------------------------
> CREATE TABLE emp (code INT4
>   , name VARCHAR(20)
>   , salary INT4
>   , 2salary INT4 default double_salary(emp)
> );
>
> CREATE FUNCTION double_salary(emp) RETURNS INT4
> AS 'SELECT $1.salay * 2 AS result' LANGUAGE 'sql'
> ;

As I understood your intent, you want the value of 2salary (BTW,
2salary would not be a valid identifier) to be twice the value of the
other attribute by default. The problem here is that $1.salay (watch
typos!) refers to the whole salary column, not to its value in the row
you are inserting. There is no way to let the function know what the
'current' row is.

If I where you, I would set a rule or a trigger, or simply run a query like this:

UPDATE emp SET salary2 = salary * 2 FROM emp WHERE salary2 ISNULL;

> Function double_salary receives an 'emp' , as you see, and there is the
> error, because if I change parameter 'emp' by a number, the error doesn't
> appear.

The current syntax does not obviously allow identifiers here. There is
probably no use for identifiers in this context, anyway. It is
normally used with constants, such as:

DEFAULT nextval('default_seq'));

> The origin of this question is that I want to know if a subclass inherits
> all attributes from the parent, included functions like double_salary in
> my example.

Attributes or functions? Functions do not belong within classes and
therefore cannot be inherited. You should view functions as
stand-alone entities. Only attributes are inherited, and it is hard to
say to what degree the inheritance mechanism remained functional in
the course of development. Since no one seems to use it, your best
source of information (other than the postgres source code and
original developers themselves) would be the files in src/test/regress


--Gene

> Hello all!
>
> I beleive that somebody knows how to answer my question...please!!

I think the reason you are not getting an (immediate) answer from the
list is that you appear to be exploring the stuff that has been pretty
much abandoned since postgres95, not because we are insensitive barbarians
with disrespect for newbies ;)

There are still some areas in postgres about which people just can't
speak with confidence.

> Make me a favor, please: Im going to send to you what I did, can you see
> that and tell me what could be bad?!.
>
> The example is:
>
>
> ---------------------------------------------------------
> CREATE TABLE emp (code INT4
>   , name VARCHAR(20)
>   , salary INT4
>   , 2salary INT4 default double_salary(emp)
> );
>
> CREATE FUNCTION double_salary(emp) RETURNS INT4
> AS 'SELECT $1.salay * 2 AS result' LANGUAGE 'sql'
> ;

As I understood your intent, you want the value of 2salary (BTW,
2salary would not be a valid identifier) to be twice the value of the
other attribute by default. The problem here is that $1.salay (watch
typos!) refers to the whole salary column, not to its value in the row
you are inserting. There is no way to let the function know what the
'current' row is.

If I where you, I would set a rule or a trigger, or simply run a query like this:

UPDATE emp SET salary2 = salary * 2 FROM emp WHERE salary2 ISNULL;

> Function double_salary receives an 'emp' , as you see, and there is the
> error, because if I change parameter 'emp' by a number, the error doesn't
> appear.

The current syntax does not obviously allow identifiers here. There is
probably no use for identifiers in this context, anyway. It is
normally used with constants, such as:

DEFAULT nextval('default_seq'));

> The origin of this question is that I want to know if a subclass inherits
> all attributes from the parent, included functions like double_salary in
> my example.

Attributes or functions? Functions do not belong within classes and
therefore cannot be inherited. You should view functions as
stand-alone entities. Only attributes are inherited, and it is hard to
say to what degree the inheritance mechanism remained functional in
the course of development. Since no one seems to use it, your best
source of information (other than the postgres source code and
original developers themselves) would be the files in src/test/regress


--Gene

"Gene Selkov Jr." <selkovjr@xnet.com> writes:
> ... Only attributes are inherited, and it is hard to
> say to what degree the inheritance mechanism remained functional in
> the course of development. Since no one seems to use it,

I use inheritance of table attributes constantly --- it seems to work
fine.  Someone else recently contributed patches for pg_dump that made
it do the right thing with inherited constraints, so I'm not the only
developer who uses inheritance...

            regards, tom lane