showing plpgsql function source code 1

[smeaggie]

Hi everybody,

I succesfully created some plpgsql trigger functions, but now want to know how exactly the functions source code was. I didn't find any command to show the functions source code? Is there such command or are the functions compiled before added and is the source code lost? I entered them directly in the psql console, not in a seperate file. Can I still view the function source code?

Thanks in advance,
Eric

 

[ceco]

from psql you can do

test=# \df+ argh()

where argh() is the function

the other way is with SQL queries, my way is to see how phpPgAdmin project is done here are the to functions there

/**
 * Returns a list of all functions in the database
 * @param $all If true, will find all available functions, if false just those in search path
 * @param $type If not null, will find all functions with return value = type 
 *
 * @return All functions
 */
function &getFunctions($all = false, $type = null) {
	if ($all) {
		$where = 'pg_catalog.pg_function_is_visible(p.oid)';
		$distinct = 'DISTINCT ON (p.proname)';
		
		if ($type) {
			$where .= " AND p.prorettype = (select oid from pg_catalog.pg_type p where p.typname = 'trigger') ";
		}
	}
	else {
		$where = "n.nspname = '{$this->_schema}'";

		$distinct = '';
	}

	$sql = "SELECT
			{$distinct}
			p.oid,
			p.proname,
			p.proretset,
			pg_catalog.format_type(p.prorettype, NULL) AS return_type,
			pg_catalog.oidvectortypes(p.proargtypes) AS arguments,
			pg_catalog.obj_description(p.oid, 'pg_proc') AS funccomment
		FROM pg_catalog.pg_proc p
		LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
		WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
		AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
		AND NOT p.proisagg
		AND {$where}
		ORDER BY p.proname, return_type
		";

	return $this->selectSet($sql);
}

/**
 * Returns all details for a particular function
 * @param $func The name of the function to retrieve
 * @return Function info
 */
function getFunction($function_oid) {
	$this->clean($function_oid);
	
	$sql = "SELECT 
				pc.oid,
				proname,
				lanname as language,
				format_type(prorettype, NULL) as return_type,
				prosrc as source,
				probin as binary,
				proretset,
				proisstrict,
				provolatile,
				prosecdef,
				oidvectortypes(pc.proargtypes) AS arguments,
				pg_catalog.obj_description(pc.oid, 'pg_proc') AS funccomment
			FROM
				pg_catalog.pg_proc pc, pg_catalog.pg_language pl
			WHERE 
				pc.oid = '$function_oid'::oid
			AND pc.prolang = pl.oid
			";

	return $this->selectSet($sql);
}