CODEWITHSUNDEEP
postgresql
Basit
Basit

Reputation: 8606

How to define variable types in stored procedures

I am new to PostgreSQL. I am learning that how you define stored procedure. I read that you can define variable type in the format like this

table.column%TYPE...
mydesc item.description%type := 'extra large size pizza';
/*
 In the example, the declaration of mydesc will result in a variable suitable for
 handling the description column in the item table of our sample database. If that
 column were defined as char(64) when we declared the variable, but later changed to 
 a char(80), the code using mydesc would still work, and PostgreSQL would create the  
 correct type of variable.
 */

Now i want to ask , if i define a function, can i also pass variable type in the same syntax. Suppose i have a table country page, in which there is a column countryid that has an integer type. Can i write the function like this

 CREATE FUNCTION insertcountrypage(pcountryid countrypage.countryid%type, pcountryinformation character varying)
 RETURNS integer AS 
   $$body$$

     ...

   $$body$$

LANGUAGE 'plsql'

see i am defining type like countrypage.countryid%type instead of integer. Is it true? Or i can use that systax only in variable declaration?

Thanks

Upvotes: 0

Views: 1010

Answers (1)

ertx
ertx

Reputation: 1544

As you create tables, columns, domains, views and so on, postgres creates their types, defined in pg_type table, afaik, you can not reference those, however - you can use domains (which are also basicaly types)

CREATE DOMAIN test_domain
  AS character varying(70);

the problem with domains is that you cant easily change their data type, however if you are using character varying type, it is possible to increment it.

say you have a domain, which is character varying(64), if you want to change it's length to 80, you should execute something like 

UPDATE pg_type set typtypmod = 84 WHERE typname = 'test_domain';

Domains may be easily passed as function parameters or table column types.

This is undocumented and possibly unsafe, altho it does work. The counter seems to be starting from -3 so you need to specify your number +4.

Upvotes: 1

Related Questions