[SA 7.3.429] Postgresql - Functions vs Procedures

[SA 7.3.429] Postgresql - Functions vs Procedures

What is the key used to split postgres functions into categories: functions and procedures?
Postgresql doesn't distinguish functions and procedures - all are considered to be functions.
But even if some wants to provide such separation, It works strange way right now.
As far as I know procedure is construct returning recordset while function returns single value.
In case of SA 7.3.429, only functions which returns void are put into 'procedures' category, while all others land in function branch

Moreover I found a bug in current DDL generator code. Actually a few.
1. DDL of functions which return recordsets (at least by returning Table object) are shown as returning a record, which I consider as a serious bug.
2. DDLs are limited to sql and plpgsql functions.

I suggest to replace the function ddl generator code by such like this:

	Code:
	SELECT string_agg(pg_get_functiondef(pp.oid), E'\n\n' ORDER BY pp.oid) FROM pg_catalog.pg_proc pp JOIN pg_catalog.pg_namespace AS pn ON pp.pronamespace=pn.oid WHERE pp.proname = :OBJECT_NAME AND pn.nspname = '$SCHEMA_NAME$'

I'm afraid I totally don't understand your question and suggestions. Where do you see SA showing procedures? Which specific query are you suggesting to change in SA options?

I'm talking about DDL Code (for postgresql 9.1) as follows:

Code:

SELECT
'CREATE FUNCTION "' || routine_schema || '"."' || routine_name || '"(' ||
array_to_string(array(
   SELECT COALESCE (p.parameter_name, '') || ' ' || LOWER (p.parameter_mode) || ' ' || REPLACE (p.data_type, '"', '')
FROM information_schema.parameters p
   WHERE p.specific_catalog = r.specific_catalog
      AND p.specific_schema = r.specific_schema
      AND p.specific_name = r.specific_name
   ORDER BY p.ordinal_position
), ',') ||
')' || CHR(10) || ' RETURNS ' || REPLACE (r.data_type, '"', '') ||
' AS' || CHR(10) || '$$' || r.routine_definition
|| '$$' || CHR(10) || 'LANGUAGE ' || r.external_language || ';'
FROM
information_schema.routines r
WHERE
r.routine_schema = :SCHEMA_NAME
AND r.routine_name = :OBJECT_NAME
AND r.external_language in ('SQL', 'PLPGSQL')
AND r.routine_definition <> 'aggregate_dummy'

This code returns function DDL with issues I listed above.
To make use of it, you have to set Options/DB Optons/SQL Assistance/DB queries/Query DDL Code to "DDL Code". Otherwise SA uses pgdump which is inapplicable for functions

Ahh, I forgot about question "Where do you see SA showing procedures?"
The procedures are seen in object tree under particular schemas (there are tables, views, functions, procedures, sequences and trigger procedures).
Please note, that only functions returning void are shown in Procedures branch (so far noticed by me)

BTW thanks for splitting trigger procedures from others (like pgAdmin does). However name of the branch should be "trigger functions" to conform postgresql standards

Thank you. That explains it. I'm forwarding this to the team