0

I want to retrieve a custom type in a function and use it as a parameter to another function to duplicate the record. In the code below I show that both retrieving a custom type and using it as a parameter to a function succeeds: see getWholeWeatherData() and addWholeWeatherData() respectively.

For the question, see dupwd(). How do I pass the output of custom type from getWholeWeatherData() as a parameter to addWholeWeatherData(), both of which succeed on their own.

$ psql --version psql (PostgreSQL) 9.3.0

DROP TABLE IF EXISTS wd;
DROP TYPE IF EXISTS wtype CASCADE;
CREATE TYPE wtype AS (
    latitude NUMERIC, 
    longitude NUMERIC, 
    temp NUMERIC, 
    barometer NUMERIC 
    );
DROP FUNCTION IF EXISTS getWholeWeatherData(INTEGER);
DROP FUNCTION IF EXISTS addWholeWeatherData(wtype);
DROP FUNCTION IF EXISTS dupwd(INTEGER);
DROP FUNCTION IF EXISTS tryAddWholeWeatherData();
CREATE TABLE wd
(
    wo_id SERIAL PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    latitude NUMERIC(9, 6) NOT NULL,
    longitude NUMERIC(9, 6) NOT NULL,
    temp NUMERIC(4, 1),
    barometer NUMERIC(4, 2),
    comment TEXT
);

CREATE OR REPLACE FUNCTION getWholeWeatherData(observ_id INTEGER)
RETURNS wtype AS $$
DECLARE
    returnrec wtype%ROWTYPE;
BEGIN
    SELECT INTO returnrec 
        latitude,
        longitude,
        temp,
        barometer
    FROM wd WHERE wo_id = observ_id;
    RETURN returnrec;
END;
$$ LANGUAGE plpgsql;

-- This works
CREATE OR REPLACE FUNCTION addWholeWeatherData(weather wtype)
RETURNS INTEGER AS $$
DECLARE
    observ_id INTEGER;
BEGIN
    INSERT INTO wd (
        datetime,
        latitude,
        longitude,
        temp,
        barometer,
        comment
    )
    VALUES (
        localtimestamp,
        weather.latitude,
        weather.longitude,
        weather.temp,
        weather.barometer,
        'just a test'
    )
    RETURNING wo_id INTO observ_id;
    RETURN observ_id;
END;
$$ LANGUAGE plpgsql;

-- This works
CREATE OR REPLACE FUNCTION tryAddWholeWeatherData()
RETURNS INTEGER AS $$
DECLARE
    wdd wtype;
    woid INTEGER;
BEGIN
    wdd.longitude = 99.99999;
    wdd.latitude = 88.88888;
    wdd.temp = 77.77;
    wdd.barometer = 66.666;
    SELECT INTO woid addWholeWeatherData(wdd);
    RETURN woid;
END;
$$ LANGUAGE plpgsql;

-- This is the question
CREATE OR REPLACE FUNCTION dupwd(observ_id INTEGER)
RETURNS VOID AS $$
DECLARE
   dr wtype;
BEGIN
    -- ??? How to get wtype result from getWholeWeatherData() into variable dr wtype
    SELECT INTO dr getWholeWeatherData(observ_id);
    -- ??? This should work if I could get the value for dr from getWholeWeatherData()
    SELECT addWholeWeatherData(dr);
END;
$$  LANGUAGE plpgsql;

-- Get started with a record
INSERT INTO wd (
    datetime,
    latitude,
    longitude,
    temp,
    barometer,
    comment
)
VALUES (
    localtimestamp,
    12.3456,
    23.4567,
    123,
    29.345,
    'initial data'
);

SELECT * FROM wd;

-- Successfully retrieves a wtype record
SELECT getWholeWeatherData(1);

-- Successfully adds a wtype record
--SELECT tryAddWholeWeatherData();

-- duplicate a record
SELECT dupwd(1);

SELECT * FROM wd;

Here is the output of running the script:

INSERT 0 1
 wo_id |          datetime          | latitude  | longitude | temp  | barometer |   comment    
-------+----------------------------+-----------+-----------+-------+-----------+--------------
     1 | 2013-12-07 13:18:14.127465 | 12.345600 | 23.456700 | 123.0 |     29.35 | initial data
(1 row)

        getwholeweatherdata        
-----------------------------------
 (12.345600,23.456700,123.0,29.35)
(1 row)

 tryaddwholeweatherdata 
------------------------
                      2
(1 row)

 wo_id |          datetime          | latitude  | longitude | temp  | barometer |   comment    
-------+----------------------------+-----------+-----------+-------+-----------+--------------
     1 | 2013-12-07 13:18:14.127465 | 12.345600 | 23.456700 | 123.0 |     29.35 | initial data
     2 | 2013-12-07 13:18:14.129907 | 88.888880 | 99.999990 |  77.8 |     66.67 | just a test
(2 rows)

psql:q1.sql:126: ERROR:  invalid input syntax for type numeric: "(12.345600,23.456700,123.0,29.35)"
CONTEXT:  PL/pgSQL function dupwd(integer) line 6 at SQL statement
 wo_id |          datetime          | latitude  | longitude | temp  | barometer |   comment    
-------+----------------------------+-----------+-----------+-------+-----------+--------------
     1 | 2013-12-07 13:18:14.127465 | 12.345600 | 23.456700 | 123.0 |     29.35 | initial data
     2 | 2013-12-07 13:18:14.129907 | 88.888880 | 99.999990 |  77.8 |     66.67 | just a test
(2 rows)

1 Answer 1

1

In a plpgsql function you have to use PERFORM in place of SELECT when you call a function and want to discard the return value.

So, in your function dupwd():

SELECT addWholeWeatherData(dr);
PERFORM addWholeWeatherData(dr);

Of course, you can simplify the call. You don't need an intermediate variable for the presented simple case. You don't even need a separate function. Just:

SELECT addWholeWeatherData(getWholeWeatherData(observ_id));
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.