up vote 1 down vote favorite

Hello i want to return a multiple row from a function but i have this persistant error anybody can give me a hand, thanks!!

Here is the error code:

    ERROR:  invalid input syntax for integer: "<html><HEAD id='header' father='*html'>
        <title id="ttl" father="*head">KarinApp(Karina Application Web Maker)</title>
                <link id="favIcon" href="http://www.karinapp.com/favicon.ico" rel="SHORTCUT ICON" />
            <link id="jQueryUI" type="text/css" href="http://www.karinapp.com/modules/general/css/karinapp-style/jquery-ui-1.8.4.custom.css" rel="stylesheet" />
        <link id="general_css" father="*head" rel="stylesheet" type="text/css" href="http://www.karinapp.com/modules/appgen/css/main.css" />
        <script id='SCRIPT1' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/jQuery.js" father="*head"><!--empty--></script>
        <script id='SCRIPT2' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.core.js"><!--empty--></script>
        <script id='SCRIPT3' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.widget.js"><!--empty--></script>
        <script id='SCRIPT4' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.mouse.js"><!--empty--></script>
        <script id='SCRIPT5' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.draggable.js"><!--empty--></script> 
        <script id='SCRIPT6' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.droppable.js"><!--empty--></script>
        <script id='SCRIPT8' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.position.js"><!--empty--></script>
        <script id='SCRIPT9' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.resizable.js">\n<!--empty--></script>
        <script id='SCRIPT10' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.dialog.js"><!--empty--></script>
        <script id='SCRIPT12' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.button.js"><!--empty--></script>
        <script id='SCRIPT12' type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/ui/jquery.ui.autocomplete.js"><!--empty--></script>
        <script id='SCRIPT13' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/general.js"><!--empty--></script>
        <script id='SCRIPT14' type="text/javascript" father="*head" src="http://www.karinapp.com/modules/general/scripts/Catcher.js"><!--empty--></script>
        <script id='SCRIPT15' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/loadPage.js"><!--empty--></script>
 <SCRIPT id="accordionScr" father="*head"  type="text/javascript"          src="/modules/general/scripts/ui/jquery.ui.accordion.js"><!--empty--></SCRIPT>       
<script id='SCRIPT16' language="javascript" type="text/javascript" src="http://www.karinapp.com/modules/general/scripts/editor/edit_area_full.js"><!--empty--></script>
        <script id='SCRIPT17' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/appgen/scripts/main.js"><!--empty--></script>
        <script id='SCRIPT18' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/appgen/scripts/config.js"><!--empty--></script>
        <script id='SCRIPT19' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/appgen/scripts/style.js"><!--empty--></script>
                <script id='HandleScr' father="*head" type="text/javascript" src="/modules/appgen/scripts/handle.js"><!--empty--></script>
        <script id='SCRIPT20' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/appgen/scripts/scripts.js"><!--empty--></script>
        <script id='SCRIPT21' father="*head" type="text/javascript" src="http://www.karinapp.com/modules/appgen/scripts/properties.js"><!--empty--></script>

        <script id='SCRIPT22' type="text/javascript">
            window.onload = function(){
                postLoad();
            }
                        function __init__(){
                                 main();
                        }
        </script>
<SCRIPT father='*head' id="batuteJS" type="text/javascript" src="/modules/appgen/scripts/batute.js"><!--empty--></SCRIPT>
        </HEAD>
<BODY id="general_bod">
<DIV id="body" father="*html"><!--empty--></DIV>
                                <DIV id="confPg" father="*body">
                <LABEL id="radio0_lab" father="confPg">
                    <INPUT type="radio" id="radio0" father="radio0_lab" name="cnfpg" value="save"/>Modificar pagina</LABEL><BR/>
                <LABEL id="radio1_lab" father="confPg">
                    <INPUT type="radio" id="radio1" father="radio1_lab" name="cnfpg" value="add2pg"/>Salvar pagina como</LABEL>
            </DIV></BODY></html>"
CONTEXT:  PL/pgSQL function "mdlreg" line 66 at SQL statement
In statement:
SELECT * FROM mdlreg('appgen', 'karinapp.com', 'dev', '186.120.130.244')

Here is the function.

DECLARE regmoment TIMESTAMP;
DECLARE mdlid bigint;

DECLARE regid visitsmdl_reg%ROWTYPE;
DECLARE mdlcode pages_reg%ROWTYPE;
DECLARE mdNm modules_reg%ROWTYPE;


BEGIN
regmoment = CURRENT_TIMESTAMP;

SELECT modules_reg.id INTO mdlid 
FROM domain_reg, sbdomain_reg, 
modules_reg, sbdomdl_asc
WHERE(modules_reg.mdname = $1 AND 
sbdomdl_asc.module = modules_reg.id AND 
sbdomdl_asc.domain = sbdomain_reg.id AND 
sbdomain_reg.domain = domain_reg.id AND
domain_reg.dname = $2 AND 
sbdomain_reg.sbname = $3);

IF mdlid IS NULL  THEN
    SELECT modules_reg.id INTO mdlid 
    FROM domain_reg, sbdomain_reg, 
    modules_reg, sbdomdl_asc
    WHERE(modules_reg.indexmdl = 't' AND 
    sbdomdl_asc.module = modules_reg.id AND 
    sbdomdl_asc.domain = sbdomain_reg.id AND 
    sbdomain_reg.domain = domain_reg.id AND
    domain_reg.dname = $2 AND 
    sbdomain_reg.sbname = $3);
    IF mdlid IS NULL OR mdlname != 'index' THEN
          INSERT INTO visitsmdl_reg (module, regtime, machaddr) VALUES(3, regmoment, $4);

          SELECT visitsmdl_reg.id, pages_reg.code
          INTO regid, mdlcode
          FROM modules_reg, visitsmdl_reg, pages_reg 
          WHERE(visitsmdl_reg.regtime = regmoment
          AND modules_reg.id = 3
          AND modules_reg.id = pages_reg.mdl);

          RETURN (regid || mdlcode);

    ELSE
    INSERT INTO visitsmdl_reg (module, regtime, machaddr) VALUES(mdlid, regmoment, $4);

    SELECT visitsmdl_reg.id, pages_reg.code,  modules_reg.mdname
    INTO regid, mdlcode, mdNm
    FROM modules_reg, visitsmdl_reg, pages_reg 
        WHERE(visitsmdl_reg.regtime = regmoment
    AND visitsmdl_reg.module = mdlid 
        AND modules_reg.id = mdlid
        AND pages_reg.mdl = mdlid);


    RETURN (regid || mdlcode || mdNm);

    END IF;

ELSE


INSERT INTO visitsmdl_reg (module, regtime, machaddr) VALUES(mdlid, regmoment, $4);


SELECT visitsmdl_reg.id, pages_reg.code
INTO regid, mdlcode
FROM modules_reg, visitsmdl_reg, pages_reg WHERE(visitsmdl_reg.regtime = regmoment 
AND visitsmdl_reg.module = modules_reg.id AND modules_reg.mdname = $1
AND modules_reg.id = pages_reg.mdl);


RETURN (regid || mdlcode);

END IF;
END;

Thanks in advance!

share|improve this question
68% accept rate
2  
Apparently you are trying to insert HTML code into an integer column with the INSERT statement at line 66. – a_horse_with_no_name Mar 24 '11 at 7:09
According to the pgAdmin III the mistake is on line 47. This is the message: SQL state: 22P02 Context: PL/pgSQL function "mdlreg" line 47 at SQL statement But, thanks!! – hidura Mar 24 '11 at 13:10
feedback

1 Answer

up vote 1 down vote accepted

Regardless of what line the error message says, here is a simpel example that raises the error.

CREATE TABLE issue_9000(f INTEGER);
INSERT INTO issue_9000 (f)
SELECT 'ABCDEFG';

And the output from PGADMIN

ERROR:  invalid input syntax for integer: "ABCDEFG"
LINE 2: SELECT 'ABCDEFG';
               ^

********** Error **********

ERROR: invalid input syntax for integer: "ABCDEFG"
SQL state: 22P02
Character: 35

Your trying to stuff characters into integer like a_horse_with_no_name commented.

share|improve this answer
Comment out the insert and hardcore some values into your last selecdt and see what happens. – JustBob Mar 24 '11 at 15:09
I figure out the mistake, i create a custom type and used to cross the data. But thanks! – hidura Mar 25 '11 at 13:03
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.