System Stored Procedures
 
System Stored Procedures
A Reference to PSQL System Stored Procedures
Stored procedures are covered in the following topic:
• PSQL System Stored Procedures
PSQL System Stored Procedures
The system stored procedures help you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a psp_ prefix.
Note Since the system stored procedure names use the psp_ prefix, do not create any stored procedures with the psp_ prefix. Any user-created stored procedure with the same name as that of a system stored procedure will never be executed.
The following table lists the system stored procedures currently supported.
Table 52 PSQL System Stored Procedures
Unless otherwise noted, the code examples for the system stored procedures refer to the Demodata sample database provided with PSQL or to PSQL system tables. For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started With PSQL.
If you execute a system stored procedure in the context of a database (for example, A) and try to obtain information from a secured database (for example, B), an error message appears. You cannot obtain information from a secured database from any other database.
psp_columns
Returns the list of columns and their corresponding information for a specified table, from the current database or the specified database.
Syntax
call psp_columns(['database_qualifier'],'table_name', ['column_name'])
Arguments
 
Table 53 psp_columns Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
(no default value)
Name of the table whose column information is required
column_name
VARCHAR(255)
All columns for the specified table
Column name of the table specified
Returned Result Set
 
Table 54 psp_columns Returned Result Set  
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the table owner. Table owner is reserved for future use. It currently returns empty (NULL).
TABLE_NAME
VARCHAR(255)
Name of the table
COLUMN_NAME
VARCHAR(255)
Column name of the table
DATA_TYPE
SMALLINT
Data type code of the column. See PSQL Transactional and Relational Data Types.
TYPE_NAME
VARCHAR (32)
Name of the data type of the column corresponding to DATA_TYPE value
PRECISION
INTEGER
The precision of the column if the data type is Decimal, Numeric, and so forth. See Precision and Scale of Decimal Data Types.
LENGTH
INTEGER
The length of the column.
SCALE
SMALLINT
The scale of the column if the data type is Decimal, Numeric, and so forth.
RADIX
SMALLINT
Base for numeric data types
NULLABLE
SMALLINT
Specifies nullability:
1 - NULL allowed
0 - NULL not allowed
REMARKS
VARCHAR(255)
Remarks field
Examples
create table tx (c_binary BINARY(10),
c_char CHAR(10),
c_tinyint TINYINT,
c_smallint SMALLINT,
c_int INT,
c_bigint BIGINT,
c_utinyint UTINYINT)
call psp_columns(, 'tx',)
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Data_type
Type_name
P
L
S
R
N
R
'demodata'
Null
tx
C_binary
-2
Binary
10
10
Null
Null
1
Null
'demodata'
Null
tx
C_char
-1
Char
10
10
Null
Null
1
Null
'demodata'
Null
tx
C_tinyint
-6
Tinyint
Null
1
0
10
1
Null
.....
 
 
 
 
 
 
 
 
 
 
 
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks
============ 
Assume that you have a database named mydatabase that contains a table named tx.
call psp_columns('mydatabase', 'tx', )
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Data_type
Type_name
P
L
S
R
N
R
'wsrde'
Null
tx
C_binary
-2
Binary
10
10
Null
Null
1
Null
'wsrde'
Null
tx
C_char
-1
Char
10
10
Null
Null
1
Null
'wsrde'
Null
tx
C_tinyint
-6
Tinyint
Null
1
0
10
1
Null
.....
 
 
 
 
 
 
 
 
 
 
 
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks
============ 
call psp_columns('mydatabase', 'tx', 'c_binary')
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Data_type
Type_name
P
L
S
R
N
R
'wsrde'
Null
tx
C_binary
-2
Binary
10
10
Null
Null
1
Null
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
database_qualifier is an undefined database
Unable to open table: X$File
table_name is undefined in the database
No error is returned and no results are returned
table_name is null
Table name cannot be null
table_name is a blank string
Table name cannot be a blank string
column_name is a blank string
Column name cannot be a blank string
column_name is undefined in the table
No error is returned and no results are returned
psp_column_attributes
Returns the list of column attributes and the corresponding information from the current database or the specified database.
Syntax
call psp_column_attributes(['database_qualifier'], ['table_name'], ['column_name'])
Arguments
 
Table 55 psp_column_attributes Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database you are logged in
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
All tables for the specified database
Name of the table whose column information is required
column_name
VARCHAR(255)
All columns for the specified table
Column name of the table specified
Returned Result Set
 
Table 56 psp_column_attributes Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the table owner
TABLE_NAME
VARCHAR(255)
Name of the table
COLUMN_NAME
VARCHAR(255)
Column name of the table
ATTRIB_TYPE
CHAR(10)
"Default" if a default value has been assigned to the column
"Collate" if the column uses a collating sequence
"L" if the column has a logical positioning
Null or blank for all other types of attributes
ATTRIB_SIZE
USMALLINT
Size of the column attribute
ATTRIB_VALUE
LONGVARCHAR
Value of the column attribute
Examples
create table tx (c_binary binary (10) default 01,
c_char char (11) default 'thisisatest',
c_tinyint TINYINT,
c_SMALLINT SMALLINT,
c_int INT,
c_bigint BIGINT,
c_utinyint uTINYINT)
call psp_column_attributes(, , )
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Attrib_Type
Attrib_Size
Attrib_Value
'demodata'
Null
tx
C_binary
Default
2
01
'demodata'
Null
tx
C_char
Default
11
'Thisisatest'
============ 
create table tlogicalmv (col1 integer, col2 char(20))
alter table tlogicalmv psql_move col1 to 2
call psp_column_attributes(, 'tlogicalmv' , )
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Attrib_Type
Attrib_Size
Attrib_Value
'demodata'
Null
tlogicalmv
col2
L
1
1
'demodata'
Null
tlogicalmv
col1
L
1
2
============ 
call psp_column_attributes(, 'tx', 'c_binary')
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Attrib_Type
Attrib_Size
Attrib_Value
'demodata'
Null
tx
C_binary
Default
2
01
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
database_qualifier is an undefined database
Unable to open table: X$File
table_name is undefined in the database
No error is returned and no results are returned
table_name is null
Table name cannot be null
table_name is a blank string
Table name cannot be a blank string
column_name is a blank string
Column name cannot be a blank string
column_name is undefined in the table
No error is returned and no results are returned
psp_column_rights
Returns the list of column rights and corresponding information for the specified table, from the current database or the specified database.
Note This system stored procedure returns the list of column rights only if it has been explicitly specified using the GRANT syntax.
Syntax
call psp_column_rights(['database_qualifier'], 'table_name', ['column_name'], ['user_name'])
Arguments
 
Table 57 psp_column_rights Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
(no default value)
Name of the table for which rights have been specified
column_name
VARCHAR(255)
All columns of the specified table
Name of the column whose rights are to be obtained
user_name
VARCHAR(255)
Current user
Name of the user for whom the list of column rights need to be obtained. Pattern matching is supported.
Returned Result Set
 
Table 58 psp_column_rights Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the owner of the table
USER_NAME (GRANTEE)
VARCHAR(255)
Name of the user
TABLE_NAME
VARCHAR(255)
Name of the table
COLUMN_NAME
VARCHAR(255)
Name of the column for which the different rights have been granted
RIGHTS
VARCHAR(12)
One of the following values:
SELECT
UPDATE
INSERT
Examples
After granting the following permissions on table Dept in the Demodata database, retrieve the column permissions:
GRANT SELECT(Name, Building_Name) ON Dept TO John
GRANT UPDATE(Name) ON Dept TO Mary
GRANT INSERT(Building_Name) ON Dept TO John
 
Call psp_column_rights(,'Dept', ,'%')
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Column_name
Rights
Demodata
Null
John
Dept
Name
SELECT
Demodata
Null
John
Dept
Building_name
SELECT
Demodata
Null
John
Dept
Building_name
INSERT
Demodata
Null
Mary
Dept
Name
UPDATE
Note User Master has no explicit column rights defined, so psp_column_rights returns no results for that user.
Assume that user John is logged on to the database. The following statement prints column permissions on table Dept table for user John.
call psp_column_rights ('demodata', 'Dept', ,)
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Column_name
Rights
Demodata
Null
John
Dept
Building_name
INSERT
Demodata
Null
John
Dept
Building_name
SELECT
Demodata
Null
John
Dept
Name
SELECT
Note If a user has been granted rights at the table level (for example, GRANT SELECT ON Dept TO Mary), a call to psp_column_rights returns no rights. The rights were granted to the table, not to specific columns.
The following statement prints column permissions on table Dept for column Name for the current user.
call psp_column_rights ('demodata', 'dept', 'name',)
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Column_name
Rights
Demodata
Null
John
Dept
Name
SELECT
The following statement prints column permissions on table Dept for user Mary:
call psp_column_rights('demodata', 'dept', , 'Mary')
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Column_name
Rights
Demodata
Null
Mary
Dept
Name
UPDATE
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is null
Table name cannot be null.
table_name is a blank string
Table name cannot be a blank string.
column_name is a blank string
Column name cannot be a blank string.
user_name is a blank string
User name cannot be a blank string.
psp_fkeys
Returns the foreign key information for the specified table in the current database.
Syntax
call psp_fkeys(['table_qualifier'], 'pkey_table_name', ['fkey_table_name'])
Arguments
 
Table 59 psp_fkeys Arguments
Parameter
Type
Default Value
Description
table_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
pkey_table_name
VARCHAR(255)
(no default value)
Name of the table whose foreign key is associated with the primary key column
fkey_table_name
VARCHAR(255)
(no default value)
Name of the table whose foreign key information needs to be obtained
Returned Result Set
 
Table 60 psp_fkeys Returned Result Set
Column Name
Data Type
Description
PKTABLE_QUALIFIER
VARCHAR (20)
Database name of the primary key table
PKTABLE_OWNER
VARCHAR (20)
Name of the owner of the primary key table
PKTABLE_NAME
VARCHAR(255)
Name of the primary key table
PKCOLUMN_NAME
VARCHAR(255)
Column name of the primary key column.
KEY_SEQ
USMALLINT
Sequence of Keys. The value of this column corresponds to Xi$Part in X$Index. See X$Index.
FKTABLE_QUALIFIER
VARCHAR (20)
Database name of the foreign key table
FKTABLE_OWNER
VARCHAR (20)
Name of the owner of the foreign key table
FKTABLE_NAME
VARCHAR(255)
Name of the foreign key table
FKCOLUMN_NAME
VARCHAR(255)
Column name of the foreign key column.
UPDATE_RULE
Utinyint
Update Rule
DELETE_RULE
Utinyint
Delete Rule
PK_NAME
VARCHAR(255)
Name of the primary key
FK_NAME
VARCHAR(255)
Name of the foreign key
Examples
CREATE TABLE Employee
(
Id INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
SupId INTEGER NOT NULL
)
 
ALTER TABLE Employee
ADD CONSTRAINT EmpPkey
PRIMARY KEY(Id)
 
ALTER TABLE Employee
ADD CONSTRAINT ForgnKey
FOREIGN KEY(SupId) REFERENCES
Employee(Id) ON DELETE CASCADE
 
call psp_fkeys(,'Employee',)
Result Set
PkQ
PkO
PkT
PkCol
Seq
FkQ
FkO
FkT
FkCol
UR
DR
PK
FK
Demodata
Null
Employee
Id
0
Demodata
Null
Employee
Supid
1
2
EmpPkey
ForgnKey
Legend: PkQ = Pkey_ table_ qualifier; PkO = Pkey_table_owner; PkT = Pktable_ name; PkCol = Pk_ column_ name; Seq = Key_seq; FkQ = Fktable_qualifier; FkO = Fktable_owner; FkT = Fktable_name; FkCol = Fkcolumn_name; UR = Update_rule; DR = Delete_rule; Pk = Pk_ name; FK = Fk_ name
Error Conditions
Condition
Error Message
table_qualifier is a blank string
Table name cannot be a blank string.
pKey_table_name is a blank string
Primary key table name cannot be a blank string.
pKey_table_name is null
No argument or default value supplied. Argument: 2.
fKey_table_name is a blank string
Foreign key table name cannot be a blank string.
psp_groups
Returns the list of groups and the corresponding information from the current database or the specified database.
Syntax
call psp_groups(['database_qualifier'], ['group_name'])
Arguments
 
Table 61 psp_groups Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
group_name
VARCHAR(255)
(no default value)
Name of the group used to return group information. Pattern matching is supported.
Returned Result Set
 
Table 62 psp_groups Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR (20)
Name of the database
GROUP_ID
USMALLINT
Group Id
GROUP_NAME
VARCHAR (255)
Name of the group
Examples
Assume that the Demodata sample database has two groups defined: DevGrp1 and DevGrp2.
call psp_groups(,)
Result Set
Database_qualifier
Group_Id
Group_Name
Demodata
1
PUBLIC
Demodata
2
DevGrp1
Demodata
3
DevGrp2
============ 
call psp_groups('Demodata', 'D%')
Result Set
Database_qualifier
Group_Id
Group_Name
Demodata
2
DevGrp1
Demodata
3
DevGrp2
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
group_name is a blank string
Group name cannot be a blank string.
psp_help_sp
Returns the definition text of a given stored procedure from the current database or the specified database.
Syntax
call psp_help_sp('[database_qualifier'], 'procedure_name')
Arguments
 
Table 63 psp_help_sp - Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
procedure_name
CHAR(255)
(no default value)
Name of the procedure whose definition text is required. Pattern matching is not supported.
Returned Result Set
 
Table 64 psp_help_sp - Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR (20)
Name of the database
SP_TEXT
LONGVARCHAR
Stored procedure definition text
Examples
Assume that the Demodata sample database contains the following stored procedure saved as “Myproc.”
Create procedure Myproc(:a integer, OUT :b integer) as
Begin
Set :a = :a + 10;
Set :b = :a;
End
The following statement prints the definition text for stored procedure “Myproc” in the current database.
call psp_help_sp(, 'Myproc')
Result Set
Database_Qualifier
SP_TEXT
Demodata
 Create procedure Myproc(:a integer, OUT :b integer) as
 Begin
Set :a = :a + 10;
Set :b = :a;
 End
============ 
Assume that a database named “wsrde” contains the following stored procedure saved as “Myproc1.”
Create procedure Myproc1(:a integer) returns (name char(20))
as
Begin
Select name from employee where Id =:a;
End
The following statement prints the definition text for stored procedure “Myproc1” in database “wsrde.”
call psp_help_sp('wsrde', 'Myproc1')
Result Set
Database_Qualifier
SP_TEXT
wsrde
 Create procedure Myproc1(:a integer) returns (name char(20))
 as
 Begin
Select name from employee where Id =:a;
 End
Error Conditions
Condition
Error Message
database_qualifier is a blank string or null
Please enter a valid database name. Database name cannot be a blank string
procedure_name is null
No argument or default value supplied.
procedure_name is a blank string
Procedure name cannot be a blank string.
psp_help_trigger
Returns the definition text of a trigger from the current database or the specified database.
Syntax
call psp_help_trigger (['database_qualifier'], 'trigger_name')
Arguments
 
Table 65 psp_help_trigger - Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
trigger_name
VARCHAR(255)
(no default value)
Name of the trigger whose definition text is to be returned. Pattern matching is supported.
Returned Result Set
 
Table 66 psp_help_trigger - Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR (20)
Name of the database
TRIGGER_TEXT
LONGVARCHAR
Trigger definition text.
Examples
The following statement prints the definition of the 'MyInsert' trigger:
CREATE TABLE A
(
col1 INTEGER,
col2 CHAR(255)
)
 
CREATE TABLE B
 
(
col1 INTEGER,
col2 CHAR(255)
)
 
CREATE TRIGGER MyInsert
AFTER INSERT ON A
FOR EACH ROW
INSERT INTO B VALUES
(NEW.col1, NEW.col2)
 
call psp_help_trigger(,'MyIns%')
Result Set
Database_Qualifier
TRIGGER_TEXT
Demodata
CREATE TRIGGER MyInsert
AFTER INSERT ON A
FOR EACH ROW
INSERT INTO B VALUES
(NEW.col1, NEW.col2);
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string
trigger_name is null
No argument or default value supplied.
trigger_name is a blank string
Trigger name cannot be a blank string.
psp_help_udf
Returns the text of a given user-defined function (UDF) from the current database or the specified database.
Syntax
call psp_help_udf (['database_qualifier'], 'udf_name')
Arguments
 
Table 67 psp_help_udf - Arguments
Parameter
Type
Default Value
Description
Database_qual
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
udf_name
VARCHAR(255)
(no default value)
Name of the user-defined function whose function text is required. Pattern matching is supported.
Returned Result Set
 
Table 68 psp_help_udf - Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR (20)
Name of the database
UDF_TEXT
LONGVARCHAR
The text of the User Defined Function
Examples
call psp_help_udf(, 'Myfunction')
Result Set
Database_Qualifier
UDF_TEXT
Demodata
 Create function Myfunction(:a integer) Returns integer
 as
 Begin
Return :a * :a;
 End
============ 
call psp_help_udf('mydbase', 'Get%')
Result Set
Database_Qualifier
UDF_TEXT
wsrde
 CREATE FUNCTION GetSmallest(:A integer, :B Integer)
 RETURNS Integer
 AS
 BEGIN
DECLARE :smallest INTEGER
IF (:A < :B ) THEN
SET :smallest = :A;
ELSE
SET :smallest = :B;
END IF;
RETURN :smallest;
 END
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string
udf_name is null
No argument or default value supplied.
udf_name is a blank string
User-defined function name cannot be a blank string.
psp_help_view
Returns the definition text of a view, from the current database or the specified database.
Syntax
call psp_help_view(['database_qualifier'], 'view_name')
Arguments
 
Table 69 psp_help_view - Arguments
Parameter
Type
Default Value
Description
Database_qual
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
view_name
VARCHAR(255)
(no default value)
Name of the view whose definition text is required. Pattern matching is supported.
Returned Result Set
 
Table 70 psp_help_view - Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR (20)
Name of the database
VIEW_TEXT
LONGVARCHAR
View definition text.
Example
Assume that the following view exists for the Demodata sample database:
CREATE VIEW vw_Person (lastn,firstn,phone) AS
SELECT Last_Name, First_Name, Phone
FROM Person
The following statement returns the definition text for view “vw_Person” in the Demodata database.
call psp_help_view(,'vw_Person')
or
call psp_help_view(,'vw_%')
Result Set
Database_Qualifier
VIEW_TEXT
Demodata
SELECT "T1" ."Last_Name" ,"T1" ."First_Name" ,"T1" ."Phone" FROM "Person" "T1"
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
view_name is null
No argument or default value supplied.
view_name is a blank string
View name cannot be a blank string.
psp_indexes
Returns the list of indexes defined for the specified table. For each index, it also lists the index properties as persisted in the X$Index table.
Syntax
call psp_indexes(['table_qualifier'], ['table_name'])
Arguments
 
Table 71 psp_indexes Arguments
Parameter
Type
Default Value
Description
table_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
(no default value)
Name of the table for whose indexes are to be obtained. Pattern matching is supported.
Returned Result Set
 
Table 72 psp_indexes Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the owner of the primary key table
TABLE_NAME
VARCHAR(255)
Name of the primary key table
INDEX_NAME
VARCHAR(255)
Name of the index
INDEX_TYPE
VARCHAR (20)
Type of the Index: primary, foreign, or normal
COLUMN_NAME
VARCHAR(255)
Name of the column on which index is defined
ORDINAL_POSITION
USMALLINT
Ordinal position of the index
DUPLICATES_ALLOWED
CHAR(3)
Yes, if it is a duplicate index
No, if it is not a duplicate index
UPDATABLE
CHAR(3)
Yes, if the index is updatable
No, if the index is not updatable
CASE_SENSITIVE
CHAR(3)
Yes, if the index is case-sensitive
No, if the index is not case-sensitive
ASC_DESC
CHAR(1)
D, Descending
A, Ascending
NAMED_INDEX
CHAR(3)
Yes, if it is a named index
No, if it is not a named index
Example
call psp_indexes(,)
Result Set
Qual
TO
TN
IN
IT
CN
Opos
Dup
Up
Case
A/D
NI
Demodata
Null
Billing
Student_Trans
Normal Index
Student_ID
0
No
Yes
No
A
Yes
Demodata
Null
Billing
Student_Trans
Normal Index
Transaction_Number
1
No
Yes
No
A
Yes
Demodata
Null
Billing
Student_Trans
Normal Index
Log
2
No
Yes
No
A
Yes
.....
 
 
 
 
 
 
 
 
 
 
 
Legend: Qual = Table_ qualifier; TO = Table_owner; TN = Table_name; IN = Index_name; IT = Index_type; CN = Column_name; Opos = Ordinal_position; Dup = Duplicates_allowed; UP = Updatable; Case = Case_ sensitive; A/D = Asc_desc; NI = Named_index
============ 
call psp_indexes('demodata', 'Dep%')
Result Set
Qual
TO
TN
IN
IT
CN
Opos
Dup
Up
Case
A/D
NI
Demodata
Null
Dept
Building_Room
Normal Index
Building_Name
0
Yes
Yes
Yes
A
Yes
Demodata
Null
Dept
Building_Room
Normal Index
Room_Number
1
Yes
Yes
No
A
Yes
Demodata
Null
Dept
Dept_Head
Normal Index
Head_Of_Dept
0
No
Yes
No
A
Yes
Demodata
Null
Dept
Dept_Name
Normal Index
Name
0
No
Yes
Yes
A
Yes
Legend: Qual = Table_ qualifier; TO = Table_owner; TN = Table_name; IN = Index_name; IT = Index_type; CN = Column_name; Opos = Ordinal_position; Dup = Duplicates_allowed; UP = Updatable; Case = Case_ sensitive; A/D = Asc_desc; NI = Named_index
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is a blank string
View name cannot be a blank string.
psp_pkeys
Returns the primary key information for the specified table, from the current database or the database specified.
Syntax
call psp_pkeys(['pkey_table_qualifier']'table_name')
Arguments
 
Table 73 psp_pkeys Arguments
Parameter
Type
Default Value
Description
pkey_table_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
(no default value)
Name of the table whose primary key information is requested. Pattern matching is supported
Returned Result Set
 
Table 74 psp_pkeys Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the owner of the primary key table
TABLE_NAME
VARCHAR(255)
Name of the primary key table
COLUMN_NAME
VARCHAR(255)
Name of the primary key column
COLUMN_SEQ
USMALLINT
Sequence of the columns (a segmented index)
PK_NAME
VARCHAR(255)
Name of the primary key
Examples
The following statement returns the information about the primary key defined on the 'pkeytest1' table:
CREATE TABLE pkeytest1
(
col1 int NOT NULL,
col2 int NOT NULL,
col3 VARCHAR(20) NOT NULL,
PRIMARY KEY(col1, col2),
UNIQUE(col3)
)
 
call psp_pkeys(,'pkeytest1')
Result Set
Table_ qualifier
Table_owner
Table_name
Column_name
Column_Seq
PK_name
'demodata'
Null
Pkeytest1
Col1
0
PK_col1
'demodata'
Null
Pkeytest1
Col2
1
PK_col1
Error Conditions
Condition
Error Message
pkey_table_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is null
No argument or default value supplied.
table_name is a blank string
Table name cannot be a blank string.
psp_procedure_rights
Returns the list of procedure rights and corresponding information for the specified stored procedure, from the current database or the specified database. The stored procedure can be a trusted or a non-trusted one. See Trusted and Non-Trusted Objects.
Syntax
call psp_procedure_rights(['database_qualifier'], ['procedure_name'], ['user_name'])
Arguments
Table 75 psp_procedure_rights Arguments
Parameter
Type
Default Value
Description
database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
procedure_name
VARCHAR(255)
(no default value)
Name of the procedure for which rights are specified. Pattern matching is supported.
user_name
VARCHAR(255)
Current user
Name of the user for whom the list of procedure rights needs to be obtained. Pattern matching is supported.
Returned Result Set
Table 76 psp_procedure_rights Returned Result Set
Column Name
Data Type
Description
PROCEDURE_QUALIFIER
VARCHAR (20)
Name of the database
PROCEDURE_OWNER
VARCHAR (20)
Name of the owner of the procedure
USER_NAME (GRANTEE)
VARCHAR(255)
Name of the user
PROCEDURE_NAME
VARCHAR(255)
Name of the procedure
RIGHTS
VARCHAR(12)
One of the following values:
ALTER
EXECUTE
Note that RIGHTS pertains only to procedures in a database that uses V2 metadata.
Examples
Assume that the following permissions exist for the Demodata sample database:
GRANT EXECUTE ON PROCEDURE Dept1_Proc TO John
GRANT ALTER ON PROCEDURE Dept1_Proc TO Mary
GRANT ALTER ON PROCEDURE Dept1_Proc TO John
GRANT EXECUTE ON PROCEDURE Proc2 TO Mary
GRANT ALTER ON PROCEDURE Proc2 TO Mary
GRANT ALTER ON PROCEDURE MyProc TO Mary
The following statement prints the permissions on the “Dept1_Proc” stored procedure for user “John.”
call psp_procedure_rights(,'Dept1_Proc', 'John')
Result Set
Procedure_Qualifier
Procedure_owner
User_name
Procedure_name
Rights
Demodata
Null
John
Dept1_Proc
ALTER
Demodata
Null
John
Dept1_Proc
EXECUTE
The following statement prints the permissions on the “Proc2” stored procedure for user “Mary.”
call psp_procedure_rights('demodata', '%Pr%', 'M%')
Result Set
Procedure_Qualifier
Procedure_owner
User_name
Procedure_name
Rights
Demodata
Null
Mary
MyProc
ALTER
Demodata
Null
Mary
Proc2
ALTER
Demodata
Null
Mary
Proc2
EXECUTE
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
procedure_name is a blank string
Procedure name cannot be a blank string.
user_name is a blank string
User name cannot be a blank string.
psp_procedure_rights called for a database with V1 metadata
View and Stored Procedure permissions are not supported for metadata version 1.
psp_rename
Changes the name of a COLUMN, INDEX, FUNCTION, PROCEDURE, TABLE, TRIGGER or VIEW in the database to which your machine is currently connected.
Syntax
call psp_rename('object_name','new_name','object_type')
Arguments
 
Table 77 psp_rename Arguments
Parameter
Type
Description
object_name
VARCHAR(776)
The current name of the column, index, user-defined function, stored procedure, table, trigger or view.
Object_name must be specified in a particular format depending on the type of object:
Column: table_name.column_name.
Index: table_name.index_name.
Function: function_name
Procedure: procedure_name
Table: table_name
Trigger: table_name.trigger_name
View: view_name
new_name
VARCHAR(776)
A user-defined name for the object. The name must conform to the naming conventions for the type of object. See Naming Conventions in PSQL Programmer's Guide.
object_type
VARCHAR(13)
The type of object being renamed. Object_type must be one of the following: COLUMN, INDEX, FUNCTION, PROCEDURE, TABLE, TRIGGER or VIEW.
Example
The following statement renames stored procedure “checkstatus” to “eligibility” in the current database.
call psp_rename('checkstatus', 'eligibility', 'PROCEDURE')
Error Conditions
All errors returned from psp_rename use status code -5099. See -5099: Error condition pertaining to a stored procedure in Status Codes and Messages.
psp_stored_procedures
Returns the list of stored procedures and their corresponding information from the current database or the specified database.
Syntax
call psp_stored_procedures(['database_qualifier'], ['procedure_name'], ['procedure_type'])
Arguments
 
Table 78 psp_stored_procedures Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
procedure_name
VARCHAR(255)
(no default value)
Name of the stored procedure whose information is required. Pattern matching is supported.
procedure_type
VARCHAR(5)
(no default value)
'SP' returns the stored procedures
'SSP' returns the system stored procedures (this type is currently not supported)
Note System stored procedures are defined in the internal PERVASIVESYSDB database, which does not display in PSQL Control Center.
Returned Result Set
 
Table 79 psp_stored_procedures Returned Result Set
Column Name
Data Type
Description
PROCEDURE_QUALIFIER
VARCHAR (20)
Name of the database
PROCEDURE _OWNER
VARCHAR (20)
Name of the owner of the procedure
PROCEDURE _NAME
VARCHAR(255)
Name of the procedure
PROCEDURE_TYPE
VARCHAR(25)
Type of procedure. The types may be "STORED PROCEDURE" or "SYSTEM STORED PROCEDURE".
NUM_INPUT_PARAMS
INT
Returns null, because SQLPROCEDURES returns null when executed against PSQL DSN
NUM_OUTPUT_PARAMS
INT
Returns null, because SQLPROCEDURES returns null when executed against PSQL DSN
NUM_RESULT_SETS
INT
Returns null, since SQLPROCEDURES returns null when executed against PSQL DSN
REMARKS
VARCHAR(255)
Remarks
TRUSTEE
INTEGER
For V2 metadata, returns 0 for a trusted stored procedure and -1 for a non-trusted stored procedure. The TRUSTEE column is empty for V1 metadata.
Examples
Assume that the current database mydbase contains two stored procedures: myproc1 and myproc2. The following statement lists the information about them.
Call psp_stored_procedures(, ,)
Result Set
Qualifier1
Owner1
Name1
Type1
Num_input_params
Num_output_params
Num_result_sets
Remarks
Trustee
mydbase
Null
Myproc1
Stored Procedure
Null
Null
Null
Null
 
mydbase
Null
Myproc2
Stored Procedure
Null
Null
Null
Null
 
1The complete column name includes “procedure_” prepended to this name: Procedure_qualifier, procedure_owner, and so forth.
============ 
The following statement lists the information about the stored procedures in the PERVASIVESYSDB internal database.
call psp_stored_procedures('PERVASIVESYSDB', ’psp_u%’, 'SP')
Result Set
Qualifier1
Owner1
Name1
Type1
Num_input_params
Num_output_params
Num_result_sets
Remarks
Trustee
pervasivesystdb
Null
psp_udfs
Stored Procedure
Null
Null
Null
Null
 
pervasivesystdb
Null
psp_users
Stored Procedure
Null
Null
Null
Null
 
1The complete column name includes “procedure_” prepended to this name: Procedure_qualifier, procedure_owner, and so forth.
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string
table_name is a blank string
Table name cannot be a blank string.
procedure_type is a blank string
Procedure type cannot be a blank string.
procedure_type is a value other than SP, SSP, or null
Procedure type can be SP, SSP, or null.
psp_tables
Returns a list of tables along with their corresponding information, from the current database or the specified database.
Syntax
call psp_tables(['database_qualifier'], ['table_name'], ['table_type'])
Arguments
 
Table 80 psp_tables – Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
(no default value)
Name of the table whose information needs to be obtained. Pattern matching is supported.
table_type
VARCHAR(20)
(no default value)
Must be one of the following:
'User table' returns only the user tables
'System table' returns all the system tables
NULL returns all tables
Returned Result Set
 
Table 81 psp_tables – Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the table owner
TABLE_NAME
VARCHAR(255)
Name of the table
TABLE_TYPE
VARCHAR (15)
System table - if the table is a system table
User table - if the table has been created by any user
REMARKS
VARCHAR(255)
Remarks
FILE_LOCATION
VARCHAR(255)
Location where the file is saved
Examples
call psp_tables(,,)
Result Set
Table_Qualifier
Table_owner
Table_name
Table_Type
Remarks
File_location
Demodata
Null
X$file
System table
Null
File.ddf
Demodata
Null
X$field
System table
Null
Field.ddf
Demodata
Null
X$Attrib
System table
Null
Attrib.ddf
Demodata
Null
Billing
User table
Null
Billing.mkd
.....
 
 
 
 
 
============ 
call psp_tables(, , 'user table')
Result Set
Table_Qualifier
Table_owner
Table_name
Table_Type
Remarks
File_location
Demodata
Null
Class
User table
Null
class.mkd
Demodata
Null
Billing
User table
Null
Billing.mkd
.....
 
 
 
 
 
============ 
call psp_tables(, , 'system table')
Result Set
Table_Qualifier
Table_owner
Table_name
Table_Type
Remarks
 
File_location
Demodata
Null
X$file
System table
Null
File.ddf
Demodata
Null
X$field
System table
Null
Field.ddf
Demodata
Null
X$Attrib
System table
Null
Attrib.ddf
.....
 
 
 
 
 
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is a blank string
Table name cannot be a blank string.
table_type is a blank string
Table type cannot be a blank string.
table_type is something other than 'system table,' 'user table,' or null
Table type can be system table, user table or null.
psp_table_rights
Returns the list of table rights and corresponding information for the specified table, from the current database or the specified database.
Syntax
call psp_table_rights(['database_qualifier'], ['table_name'], ['user_name'])
Arguments
 
Table 82 psp_table_rights Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
All tables
Name of the table for which rights have are specified. Pattern matching is supported.
user_name
VARCHAR(255)
Current user
Name of the user for whom the list of table rights needs to be obtained. Pattern matching is supported.
Returned Result Set
 
Table 83 psp_table_rights Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR (20)
Name of the database
TABLE_OWNER
VARCHAR (20)
Name of the owner of the table
USER_NAME (GRANTEE)
VARCHAR(255)
Name of the user
TABLE_NAME
VARCHAR(255)
Name of the table
RIGHTS
VARCHAR(12)
One of the following values:
SELECT
ALTER
DELETE
INSERT
REFERENCES
SELECT
UPDATE
Examples
Assume that the following permissions exist for the Demodata sample database.
GRANT SELECT ON Dept TO John
GRANT ALTER ON Dept TO John
GRANT DELETE ON Dept TO John
GRANT SELECT ON Class TO Mary
GRANT ALTER ON Class TO Mary
The following statement prints the table permissions on the “Dept” table for user “John” in the current database (Demodata).
call psp_table_rights(,'Dept', 'John')
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Rights
Demodata
Null
John
Dept
ALTER
Demodata
Null
John
Dept
DELETE
Demodata
Null
John
Dept
SELECT
============ 
Assume that user “Mary” is logged on the database. The following statement prints the table permissions on the “Class” table in the Demodata database for the current user (Mary).
call psp_table_rights('demodata', 'cl%', )
Result Set
Table_Qualifier
Table_owner
User_name
Table_name
Rights
Demodata
Null
Mary
Class
SELECT
Demodata
Null
Mary
Class
ALTER
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is a blank string
Table name cannot be a blank string.
user_name is a blank string
User name cannot be a blank string.
psp_triggers
Returns the list of triggers and their corresponding information from the current database or the specified database.
Syntax
call psp_triggers(['database_qualifier'], ['table_name'])
Arguments
 
Table 84 psp_triggers Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
table_name
VARCHAR(255)
All tables
Name of the table for which the trigger is defined. Pattern matching is supported.
Returned Result Set
 
Table 85 psp_triggers Returned Result Set
Column Name
Data Type
Description
TRIGGER_QUALIFIER
VARCHAR (20)
Name of the database
TRIGGER_OWNER
VARCHAR (20)
Name of the owner of the Trigger
TABLE_NAME
VARCHAR(255)
Name of the table for which the trigger is defined.
TRIGGER_NAME
VARCHAR(255)
Name of the trigger
ISUPDATE
UTINYINT
Is set if it is an update trigger
ISDELETE
UTINYINT
Is set if it is an delete trigger
ISINSERT
UTINYINT
Is set if it an insert trigger
ISAFTER
UTINYINT
Is set if the trigger action time is "after"
ISBEFORE
UTINYINT
Is set if the trigger action time is "before"
REMARKS
VARCHAR(255)
Remarks
Examples
Assume that the current database is mydbase. The following statement returns the list of triggers defined in the database:
CREATE TABLE A
(
col1 INTEGER,
col2 CHAR(255)
) ;
 
CREATE TABLE B
(
col1 INTEGER,
col2 CHAR(255)
) ;
 
CREATE TRIGGER Insert
AFTER INSERT ON A
FOR EACH ROW
INSERT INTO B VALUES
(NEW.col1, NEW.col2);
 
call psp_triggers(,)
Result Set
Trigger_qualifier
Trigger_owner
Table_name
Trigger_name
isupdate
isdelete
isinsert
isafter
isbefore
Remarks
mydbase
Null
A
Insert
0
0
1
0
0
Null
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
table_name is a blank string
Table name cannot be a blank string.
psp_udfs
Returns the list of user-defined functions (UDF) and their corresponding information from the current database or the specified database.
Syntax
call psp_udfs(['database_qualifier'], ['udf_name'])
Arguments
 
Table 86 psp_udfs Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
udf_name
VARCHAR(255)
All user-defined functions
Name of the udf whose details are needed. Pattern matching is supported.
Returned Result Set
 
Table 87 psp_udfs Returned Result Set
Column Name
Data Type
Description
UDF_QUALIFIER
VARCHAR (20)
Name of the database
UDF_OWNER
VARCHAR (20)
Name of the owner of the UDF
UDF _NAME
VARCHAR(255)
Name of the UDF
UDF_TYPE
VARCHAR(25)
Type of UDF (always set to 1)
Currently, PSQL does not support any special type of UDF.
NUM_INPUT_PARAMS
INT
Returns null, because SQLPROCEDURES returns null when executed against PSQL DSN.
NUM_OUTPUT_PARAMS
INT
Returns 1, because UDFs return only scalar values
NUM_RESULT_SETS
INT
Returns 0, because UDFs do not return resultsets
REMARKS
VARCHAR(255)
Remarks
Examples
Assume that the current database mydbase has two user-defined functions: calcinterest and factorial.
call psp_udfs(, )
Result Set
UDF_ qualifier
UDF_owner
UDF_name
Udf_type
Num_ input_params
Num_ output_params
Num_result_sets
Remarks
mydbase
Null
CalcInterest
1
Null
1
0
Null
mydbase
Null
Factorial
1
Null
1
0
Null
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
udf_name is a blank string
User-defined function name cannot be a blank string.
psp_users
Returns the list of users and the corresponding information from the current database or the specified database.
Syntax
call psp_users(['database_qualifier'], ['group_name'], ['user_name'])
Arguments
 
Table 88 psp_users Arguments
Parameter
Type
Default Value
Description
database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
group_name
VARCHAR(255)
All groups (if group_name is null)
Name of the group used to return the user information. Pattern matching is supported. If group name is specified (i.e. if it is not NULL), only users belonging to the same group will be returned.
user_name
VARCHAR(255)
All users (if user_name is null)
Name of the user. Pattern matching is supported.
Returned Result Set
 
Table 89 psp_users Returned Result Set
Column Name
Data Type
Description
DATABASE_QUALIFIER
VARCHAR(20)
Name of the database
GROUP_ID
USMALLINT
Group ID of the group to which user belongs
GROUP_NAME
VARCHAR(255)
Name of the group to which user belongs
USER_ID
USMALLINT
ID of the user
USER_NAME
VARCHAR(255)
Name of the user
Examples
Assume that current database mydbase has users John, Mary, and Michael, and groups DevGrp and DevGrp1.
call psp_users(, , )
Result Set
Database_qualifier
Group_Id
Group_Name
User_Id
User_Name
Demodata
1
DevGrp
3
John
Demodata
2
DevGrp1
1
Mary
Demodata
1
DevGrp
4
Michael
============ 
call psp_users(, 'Devgrp', )
Result Set
Database_qualifier
Group_Id
Group_Name
User_Id
User_Name
Demodata
1
DevGrp
3
John
Demodata
2
DevGrp
4
Michael
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string.
user_name is null
User name cannot be a null.
group_name is a blank string
Group name cannot be a blank string.
psp_view_rights
Returns the list of list of view rights and corresponding information for the specified view, from the current database or the specified database. The view can be a trusted or a non-trusted one. See Trusted and Non-Trusted Objects.
Psp_view_rights applies only to a database using V2 metadata.
Syntax
call psp_view_rights(['database_qualifier'], ['view_name'], ['user_name'])
Arguments
Table 90 psp_view_rights Arguments
Parameter
Type
Default Value
Description
database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
view_name
VARCHAR(255)
All views (if view_name is null)
Name of the view for which rights are specified. Pattern matching is supported.
user_name
VARCHAR(255)
Current user (if user_name is null)
Name of the user for whom the list of view rights needs to be obtained. Pattern matching is supported.
Returned Result Set
Table 91 psp_view_rights Returned Result Set
Column Name
Data Type
Description
VIEW_QUALIFIER
VARCHAR (20)
Name of the database
VIEW_OWNER
VARCHAR (20)
Name of the owner of the view
USER_NAME (GRANTEE)
VARCHAR(255)
Name of the user
VIEW_NAME
VARCHAR(255)
Name of the view
RIGHTS
VARCHAR(12)
One of the following values:
ALTER
DELETE
INSERT
SELECT
UPDATE
Examples
Assume that the following permissions exist for the Demodata sample database:
GRANT SELECT ON VIEW vw_Dept TO John
GRANT ALTER ON VIEW vw_Dept TO John
GRANT DELETE ON VIEW vw_Dept TO John
GRANT SELECT ON VIEW vw_Class TO Mary
GRANT ALTER ON VIEW vw_Class TO Mary
GRANT SELECT ON VIEW vw_Class TO Prakesh
The following statement prints the view permissions on the “vw_Dept” view for user “John.”
call psp_view_rights(,'vw_Dept', 'John')
Result Set
View_Qualifier
View_owner
User_name
View_name
Rights
Demodata
Null
John
vw_Dept
ALTER
Demodata
Null
John
vw_Dept
DELETE
Demodata
Null
John
vw_Dept
SELECT
============ 
Assume that user “Mary” is logged on the database. The following statement prints the view permissions on all views in the sample database Demodata for the current user (Mary).
call psp_view_rights('demodata', , )
Result Set
View_Qualifier
View_owner
User_name
View_name
Rights
Demodata
Null
Mary
vw_Class
ALTER
Demodata
Null
Mary
vw_Class
SELECT
============ 
The following statement prints the view permissions on the “vw_Class” view for user “Mary.”
call psp_view_rights('demodata', 'vw_C%', 'Mary')
Result Set
View_Qualifier
View_owner
User_name
View_name
Rights
Demodata
Null
Mary
vw_Class
ALTER
Demodata
Null
Mary
vw_Class
SELECT
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string
view_name is a blank string
View name cannot be a blank string.
user_name is a blank string
User name cannot be a blank string.
psp_procedure_rights called for a database with V1 metadata
View and Stored Procedure permissions are not supported for metadata version 1.
psp_views
Returns the list of views along with their corresponding information, from the current database or from the database specified.
Syntax
call psp_views(['database_qualifier'], ['view_name'])
Arguments
 
Table 92 psp_views Arguments
Parameter
Type
Default Value
Description
Database_qualifier
VARCHAR(20)
Current database
Name of the database from which the details are to be obtained
view_name
VARCHAR(255)
(no default value)
Name of the view whose information is required. Pattern matching is supported.
Returned Result Set
 
Table 93 psp_views Returned Result Set
Column Name
Data Type
Description
VIEW_QUALIFIER
VARCHAR (20)
Name of the database
VIEW_OWNER
VARCHAR (20)
Name of the owner of the view
VIEW_NAME
VARCHAR(255)
Name of the view
REMARKS
VARCHAR(255)
Remarks
TRUSTEE
INTEGER
For V2 metadata, returns 0 for a trusted view and -1 for a non-trusted view. The TRUSTEE column is empty for V1 metadata.
Examples
Assume that the following view exists for a V2 metadata database named Demodata2.
CREATE VIEW vw_Person (lastn,firstn,phone) WITH EXECUTE AS 'Master' AS
SELECT Last_Name, First_Name,Phone FROM Person;
The following statement prints the list of views in the current database, which is Demodata2.
call psp_views(, ,)
Result Set
View_Qualifier
View_Owner
View_Name
Remarks
Trustee
Demodata
Null
Vw_Person
Null
0
============ 
The following statement prints the view information for the “vw_Person” view in the Demodata database.
call psp_views('demodata', 'vw_P%')
Result Set
View_Qualifier
View_Owner
View_Name
Remarks
Trustee
Demodata
Null
Vw_Person
Null
 
Error Conditions
Condition
Error Message
database_qualifier is a blank string
Please enter a valid database name. Database name cannot be a blank string
view_name is a blank string
Table name cannot be a blank string.