It is important to understand the basic nature of these five different data / symbol types :
1. 'my_tbl'
String literal unknown type . When used in SQL (built into plpgsql code or not), it is forced to a type obtained from the context . If the type cannot be determined, explicit casting may be required. For example: 'my_tbl'::text .
2. 'my_tbl'::text
The same string literal passed to type text . It may contain the name of the table, but it's really just text.
3. 'my_tbl'::regclass
object identifier (OID) for the registered class . It is displayed and can be entered as a string representing the actual name of the object ( 'my_tbl' ). The result is automatically assigned to schemas ( 'my_schema.my_tbl' ) and / or double quotes ( '"mY_TbL"' ) if it would be ambiguous or illegal otherwise. This can be a regular table, sequence, view, materialized view, composite type, etc. Details in this related answer:
- How to check if a table exists in a given schema
4. my_tbl_var my_tbl (short for my_tbl_var my_tbl%ROWTYPE )
In the DECLARE section of the plpgsql code block, declare a variable with a known string type (as a composite type). The type must be registered in the pg_class system table (the same as with the regclass variable). This is not the OID of the referenced object, but its actual string type. my_tbl_var and my_tbl are both identifiers here and cannot be parameterized. You can also use any line or record directly: (123, 'foo')::my_tbl
5. my_tbl_var record
In the DECLARE section of the plpgsql code block, an anonymous record is declared, in principle, a placeholder for an as yet unknown string type / with an still undefined structure. It can be used in most places where a string type can be used. But you cannot access these fields before a record variable is assigned.
You misled 1. , 3., and 4. and solved it using 5. ..
But here it no longer works :
You select the whole table, but the row (record) variable can only contain one row at a time. Thus, only the first is assigned and returned. As long as there is no ORDER BY , the result is arbitrary and can change at any time. Wicked trap.
Since you are now using the record type, you need to make sure that it has been assigned before you can run tests in your fields or you get exceptions for empty tables. In your case, checking record_var IS NULL almost does the same job. But there is an angle for rows with NULL in all fields: then record_var IS NULL is true. Even harder for the IS NOT NULL test. Details here:
- NON-ZERO write test does not return TRUE when setting a variable
I added a demo to the SQL script below.
The function returns a single scalar ( boolean ) value. Using:
RETURN false;
Instead:
RETURN QUERY SELECT false;
Functions
CREATE FUNCTION check_valid(_tbl regclass) RETURNS bool AS $func$ DECLARE r record; _row_ct int; BEGIN EXECUTE ' SELECT is_valid, hit_count, hit_limit FROM ' || _tbl || ' ORDER <whatever> LIMIT 1' -- replace <whatever> with your sort criteria INTO r; -- only needed columns GET DIAGNOSTICS _row_ct = ROW_COUNT; IF _row_ct = 0 THEN -- necessary, because r may not be assigned RETURN false; ELSIF NOT r.is_valid OR r.hit_count > r.hit_limit THEN RETURN false; END IF; RETURN true; END $func$ LANGUAGE plpgsql;
SQL Fiddle (with two options for the function and a demo for the IS NULL string).
Highlights
Use GET DIAGNOSTICS to find out if rows in a dynamic expression were found using EXECUTE .
The expression IF can be simplified.
The parameter is of type regclass , not just table_name. I would not use the misleading name "tablename" for this parameter. This only adds to your initial confusion. Instead, _tbl is _tbl .
If you want to also return a set of string type variables:
- Refactoring the PL / pgSQL function to return the output of various SELECT queries