Forum OpenACS Q&A: Error using __new function

Collapse
Posted by Simon Carstensen on
I have created the following __new function:

create function na_subscription__new (
    integer,    -- subscription_id
    integer,    -- owner_id
    varchar,    -- feed_url
    varchar,    -- host_url
    varchar,    -- title
    varchar,    -- description
    integer,    -- updates
    timestamp,  -- last_scanned
    varchar,    -- object_type
    timestamp,  -- creation_date
    integer,    -- creation_user
    varchar,    -- creation_ip
    integer     -- context_id
) returns integer as '
declare
    p_subscription_id   alias for $1;   -- default null
    p_owner_id          alias for $2;   -- default null
    p_feed_url          alias for $3;
    p_host_url          alias for $4;
    p_title             alias for $5;
    p_description       alias for $6;
    p_updates           alias for $7;
    p_last_scanned      alias for $8;
    p_object_type       alias for $9;   -- default ''na_subscription''
    p_creation_date     alias for $10;  -- default now()
    p_creation_user     alias for $11;  -- default null
    p_creation_ip       alias for $12;  -- default null
    p_context_id        alias for $9;   -- default null
    v_subscription_id   integer;
begin

        v_subscription_id := acs_object__new (
                             p_subscription_id,
                             p_object_type,
                             current_timestamp,
                             p_creation_user,
                             p_creation_ip,
                             p_context_id
        );

        insert into na_subscriptions
          (subscription_id, owner_id, feed_url, host_url, title, description, updates, last_scanned)
        values
          (v_subscription_id, p_owner_id, p_feed_url, p_host_url, p_title, p_description, p_updates, p_lastsca\
nned);

        PERFORM acs_permission__grant_permission(
          v_subscription_id,
          p_owner_id,
          ''admin''
    );

        return v_subscription_id;

end;' language 'plpgsql';

I am now building subscription-add.tcl, which creates a new subscription with:
db_exec_plsql new_subscription {}
Here's the corresponding query in subscription-add-postgresql.xql:
select na_subscription__new(
                :subscription_id,
                :user_id,
                :feed_url,
                :host_url,
                :title,
                :description,
                '0',
                current_timestamp,
                'na_subscription',
                current_timestamp,
                :user_id,
                :peeraddr,
                :package_id
);
But I get the following error upon visting the page:

[11/Jan/2003:19:42:02][23272.4101][-conn0-] Error: dbinit: error(localhost::bcuni,ERROR:  Function 'na_subscri\
ption__new(unknown, unknown, unknown, unknown, unknown, unknown, unknown, timestamptz, unknown, timestamptz, u\
nknown, unknown, unknown)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
): '
        select na_subscription__new(
                '4597',
                '2604',
                'http://scriptingnews.com/rss.xml',
                'http://scriptingnews.com',
                'Scripting News',
                'A weblog about scripting and stuff like that.',
                '0',
                current_timestamp,
                'na_subscription',
                current_timestamp,
                '2604',
                '62.107.42.240',
                '4353'
        );

      '
The problem seems rather basic, but my searching the forums unfortunately didn't give me anything.

Can anyone help me out and let me know what I am doing wrong?

/Simon

Collapse
Posted by Jun Yamog on
Hi Simon,

Its says that you may need to explicitly typecast it.  I had similar problem in content_item__new.  You will have to hunt down the offending parameter or typecast all.  What do I mean by that?  If the parameter for example that needs to be typecasted is subscription_id here is how you will call it:

select na_subscription__new(
                integer :subscription_id,
                :user_id,
                :feed_url,
                :host_url,
                :title,
                :description,
                '0',
                current_timestamp,
                'na_subscription',
                current_timestamp,
                :user_id,
                :peeraddr,
                :package_id
);

If explicitly typecasting it doesn't work... it must be another problem.  Hope this helps.

Collapse
Posted by Simon Carstensen on
Thank you Jun for your suggestion, but I still get the same error, although now without the long line of "unknown" parameters (so that's perhaps one step closer to a solution):
[11/Jan/2003:20:37:55][24613.4101][-conn0-] Notice: Querying '
        select na_subscription__new(
                integer '4607',
                integer '2604',
                varchar 'http://scriptingnews.com/rss.xml',
                varchar 'http://scriptingnews.com',
                varchar 'Scripting News',
                varchar 'A weblog about scripting and stuff like that.',
                integer '0',
                timestamp '2003-01-12',
                varchar 'na_subscription',
                timestamp '2003-01-12',
                integer '2604',
                varchar '62.107.42.240',
                integer '4353'
        );'
[11/Jan/2003:20:37:55][24613.4101][-conn0-] Error: Ns_PgExec: result status: 7 message: ERROR:  Function 'na_s\
ubscription__new(int4, int4, varchar, varchar, varchar, varchar, int4, timestamptz, varchar, timestamptz, int4\
, varchar, int4)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
Any other suggestions as to what may solve this?

/Simon

Collapse
Posted by Simon Carstensen on
Having specified the typecasts properly when calling na_subscription__new , I now get an error from acs_object__new:
[11/Jan/2003:21:14:57][25375.24581][-conn3-] Error: Ns_PgExec: result status: 7 message: ERROR:  Function 'acs_object__new(int4, varchar, timestamptz, int4, varchar, varchar)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
When specifying typecasts for this function (which shouldn't be necessary, browsing the code of other packages), I get this error:
[11/Jan/2003:21:13:57][25375.24581][-conn3-] Error: Ns_PgExec: result status: 7 message: ERROR:  parser: parse  error at or near "$2"

/Simon

Collapse
Posted by Roberto Mello on
Simon,

Are you sure the function was created? Check if it is in the pg_proc table.

-Roberto

Collapse
Posted by Ola Hansson on
Simon,

p_object_type and p_context_id both have the "alias for $9;" attached to them. That may be it...

Generally, one of the best ways to look for clues when debugging pl[pg]sql is to have an emacs shell ( M-x shell - https://openacs.org/education/psets/advice#Emacs ) running on the side, where you have done  a "tail -f your-aolserver-error-log", and look at the error message generated as you request the page with the offending statement - and a couple of rows before the error is often helpful.

Increasing the level of verbosity of the debug messages in the error log by manipulating postgresql.conf is sometimes necessary, but usually not, in my experience.

HTH.

Collapse
Posted by Simon Carstensen on
Thank you for the pointers, Ola. That did the trick!

Also I found the "OpenACS Object Type Administration" to be quite helpful when it came to making sure that everything had been properly created.