How to I return a "on the fly" table? Mismatch seems to make no sense-CodePudding

I am getting the error: return type mismatch in function declared to return record

I have used pg_typeof for every column returned to ensure the types were correct, but I still can't make it work.

I need the return query so that I can build another function that would READ the result and call another function (PL/PGSQL) to create the sample people. It can't be a simple INSERT as there are many rules to be followed that is already done by this other FUNCTION called create_person (Not shown here)

DROP TABLE IF EXISTS "common_last_name" CASCADE;

DROP TABLE IF EXISTS "common_first_name" CASCADE;

DROP TABLE IF EXISTS "common_nickname" CASCADE;

DROP TABLE IF EXISTS "common_honorific" CASCADE;

DROP TABLE IF EXISTS "common_suffix" CASCADE;

DROP TABLE IF EXISTS "common_stop_word" CASCADE;

DROP TABLE IF EXISTS "common_email_domain" CASCADE;

DROP TABLE IF EXISTS "common_document_type" CASCADE;

DROP TABLE IF EXISTS "common_year_of_birth" CASCADE;

DROP FUNCTION IF EXISTS _get_sample_data;

CREATE TABLE "common_last_name" (
    "last_name" text PRIMARY KEY,
    "ratio" numeric NOT NULL,
    "last_name_phonetic" text NOT NULL,
    "last_name_phonetic_alt" text,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_first_name" (
    "first_name" text PRIMARY KEY,
    "ratio" numeric NOT NULL,
    "first_name_phonetic" text NOT NULL,
    "first_name_phonetic_alt" text,
    "genre" text,
    "genre_m_f_percentage" int,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_nickname" (
    "name" text NOT NULL,
    "name_phonetic" text NOT NULL,
    "name_phonetic_alt" text NOT NULL,
    "nickname" text NOT NULL,
    "nickname_phonetic" text NOT NULL,
    "nickname_phonetic_alt" text NOT NULL,
    "genre" text NOT NULL,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_honorific" (
    "honorific" text PRIMARY KEY,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_suffix" (
    "suffix" text PRIMARY KEY,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_stop_word" (
    "stop_word" text PRIMARY KEY,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_email_domain" (
    "domain_name" text PRIMARY KEY,
    "ratio" numeric NOT NULL,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_document_type" (
    "country_iso_document_type" text PRIMARY KEY,
    "ratio" numeric NOT NULL,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE "common_year_of_birth" (
    "year" int,
    "ratio" numeric NOT NULL,
    "inserted_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    "updated_at" timestamp WITH time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

---------------------------
CREATE OR REPLACE FUNCTION _get_sample_data(p_number_of_records INT) 
RETURNS TABLE(first_name TEXT, last_name TEXT, email TEXT, phone TEXT, document TEXT, date_of_birth TIMESTAMP) 
AS $$
WITH first_names_weighted AS 
(
    SELECT
        first_name,
        SUM(ratio) OVER (
    ORDER BY
        first_name) - ratio AS lower_bound,
        SUM(ratio) OVER (
    ORDER BY
        first_name) AS upper_bound 
    FROM
        common_first_name 
),
last_names_weighted AS 
(
    SELECT
        last_name,
        SUM(ratio) OVER (
    ORDER BY
        last_name) - ratio AS lower_bound,
        SUM(ratio) OVER (
    ORDER BY
        last_name) AS upper_bound 
    FROM
        common_last_name 
),
email_domain_weighted AS 
(
    SELECT
        domain_name,
        SUM(ratio) OVER ( 
    ORDER BY
        domain_name ) - ratio AS lower_bound,
        SUM(ratio) OVER ( 
    ORDER BY
        domain_name ) AS upper_bound 
    FROM
        common_email_domain 
),
document_type_weighted AS 
(
    SELECT
        country_iso_document_type,
        SUM(ratio) OVER ( 
    ORDER BY
        country_iso_document_type ) - ratio AS lower_bound,
        SUM(ratio) OVER ( 
    ORDER BY
        country_iso_document_type ) AS upper_bound 
    FROM
        common_document_type 
),
year_of_birth_weighted AS 
(
    SELECT
        YEAR,
        SUM(ratio) OVER ( 
    ORDER BY
        YEAR ) - ratio AS lower_bound,
        SUM(ratio) OVER ( 
    ORDER BY
        YEAR ) AS upper_bound 
    FROM
        common_year_of_birth 
),
randoms AS 
(
    SELECT
        random() * ( 
        SELECT
            SUM(ratio) 
        FROM
            common_first_name ) AS f_random,
            random() * ( 
            SELECT
                SUM(ratio) 
            FROM
                common_last_name ) AS l_random,
                random() * ( 
                SELECT
                    SUM(ratio) 
                FROM
                    common_email_domain ) AS e_random,
                    random() * ( 
                    SELECT
                        SUM(ratio) 
                    FROM
                        common_document_type ) AS d_random,
                        random() * ( 
                        SELECT
                            SUM(ratio) 
                        FROM
                            common_year_of_birth ) AS y_random 
                        FROM
                            generate_series(1, p_number_of_records ) 
)
SELECT
    --r, 
    first_name AS first_name,
    last_name AS last_name,
    LOWER( SUBSTRING( first_name, 1, 2   (random() * (length(first_name) - 2))::INTEGER) || SUBSTRING( last_name, 1, 2   (random() * (length(last_name) - 2))::INTEGER) || '@' || domain_name ) AS email,
    ' 1 ' || lpad(round(random() * 999)::text, 3, '0') || ' ' || lpad(round(random() * 999)::text, 3, '0') || ' ' || lpad(round(random() * 9999)::text, 4, '0') AS phone,
    CASE
        WHEN
            POSITION('.sin' IN country_iso_document_type) > 1 
        THEN
            country_iso_document_type || '.' || lpad((round(random() * 999))::TEXT, 3, '0') || '-' || lpad((round(random() * 999))::TEXT, 3, '0') || '-' || lpad((round(random() * 999))::TEXT, 3, '0') 
        ELSE
            CASE
                WHEN
                    POSITION('.driver' IN country_iso_document_type) > 1 
                THEN
                    country_iso_document_type || '.' || chr((65   random()*25)::INT) || lpad(CAST(round(random() * 99999) AS text), 5, '0') || '-' || lpad((round(random() * 99999))::TEXT, 5, '0') || '-' || lpad((round(random() * 99999))::TEXT, 5, '0') 
                ELSE
                    CASE
                        WHEN
                            POSITION('.passport' IN country_iso_document_type) > 1 
                        THEN
                            country_iso_document_type || '.' || chr((65   random()*25)::INT) || chr((65   random()*25)::INT) || lpad((round(random() * 999999999999))::TEXT, 12, '0') 
                        ELSE
                            NULL 
                    END
            END
    END
    AS document,
    make_date(YEAR, (round(1   random() * 11))::INTEGER, 1)   make_interval(days => (round(1   random() * 30))::INTEGER) AS dob 
FROM
    randoms r 
    CROSS JOIN
        first_names_weighted f 
    CROSS JOIN
        last_names_weighted l 
    CROSS JOIN
        email_domain_weighted e 
    CROSS JOIN
        document_type_weighted d 
    CROSS JOIN
        year_of_birth_weighted y 
WHERE
    f.lower_bound <= r.f_random 
    AND r.f_random <= f.upper_bound 
    AND l.lower_bound <= r.l_random 
    AND r.l_random <= l.upper_bound 
    AND e.lower_bound <= r.e_random 
    AND r.e_random <= e.upper_bound 
    AND d.lower_bound <= r.d_random 
    AND r.d_random <= d.upper_bound 
    AND y.lower_bound <= r.y_random 
    AND r.y_random <= y.upper_bound;
END;
$$ language sql;


-- ***SAMPLE DATA
--Common email domains
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('gmail.com', 200);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('yahoo.com', 20);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('outlook.com', 20);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('hotmail.com', 20);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('icloud.com', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('yahoo.ca', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('shaw.ca', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('live.ca', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('aol.com', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('protonmal.com', 4);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('zoho.com', 4);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('startmail.com', 4);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('thexyz.com', 2);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('gmial.com', 10);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('yaho.com', 5);
INSERT INTO common_email_domain(domain_name, ratio) VALUES ('outloock.com', 5);

--Common year of birth
--1.7 ratio 18-24 age
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-18, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-19, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-20, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-21, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-22, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-23, 17);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-24, 17);
--5.2 ratio 25-44 age
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-25, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-26, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-27, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-28, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-29, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-30, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-31, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-32, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-33, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-34, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-35, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-36, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-37, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-38, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-39, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-40, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-41, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-42, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-43, 52);
INSERT INTO common_year_of_birth(year, ratio) VALUES (date_part('year', CURRENT_DATE)-44, 52);

--Common document types
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('', 1000);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('ca.sin', 100);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('ca.driver-licence', 100);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('in.passport', 8);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('us.passport', 6);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('cn.passport', 4);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('mx.passport', 4);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('br.passport', 4);
INSERT INTO common_document_type(country_iso_document_type, ratio) VALUES ('ca.passport', 1);

--Common First Names
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('John', 0.01827302261, dmetaphone('John'), dmetaphone_alt('John'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('David', 0.01546078785, dmetaphone('David'), dmetaphone_alt('David'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Robert', 0.01443493568, dmetaphone('Robert'), dmetaphone_alt('Robert'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Michael', 0.01203178307, dmetaphone('Michael'), dmetaphone_alt('Michael'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Paul', 0.009156650734, dmetaphone('Paul'), dmetaphone_alt('Paul'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Richard', 0.007790220995, dmetaphone('Richard'), dmetaphone_alt('Richard'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('James', 0.007687311565, dmetaphone('James'), dmetaphone_alt('James'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Peter', 0.00749865699, dmetaphone('Peter'), dmetaphone_alt('Peter'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('William', 0.006330570112, dmetaphone('William'), dmetaphone_alt('William'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Brian', 0.006272669254, dmetaphone('Brian'), dmetaphone_alt('Brian'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Mary', 0.006226325682, dmetaphone('Mary'), dmetaphone_alt('Mary'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Linda', 0.005831280103, dmetaphone('Linda'), dmetaphone_alt('Linda'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Daniel', 0.00570003053, dmetaphone('Daniel'), dmetaphone_alt('Daniel'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Mark', 0.005683095101, dmetaphone('Mark'), dmetaphone_alt('Mark'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Chris', 0.005489481954, dmetaphone('Chris'), dmetaphone_alt('Chris'), 'm', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Karen', 0.005429559525, dmetaphone('Karen'), dmetaphone_alt('Karen'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Jennifer', 0.005307616808, dmetaphone('Jennifer'), dmetaphone_alt('Jennifer'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Lisa', 0.005023833946, dmetaphone('Lisa'), dmetaphone_alt('Lisa'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Susan', 0.004946709088, dmetaphone('Susan'), dmetaphone_alt('Susan'), 'f', 100); 
INSERT INTO common_first_name(first_name, ratio, first_name_phonetic, first_name_phonetic_alt, genre, genre_m_f_percentage) VALUES ('Michel', 0.00491550823, dmetaphone('Michel'), dmetaphone_alt('Michel'), 'm', 100); 

--Common Last Names
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Smith',dmetaphone('Smith'), dmetaphone_alt('Smith'), 0.01321048057);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Brown',dmetaphone('Brown'), dmetaphone_alt('Brown'), 0.007484346222);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Tremblay',dmetaphone('Tremblay'), dmetaphone_alt('Tremblay'), 0.007333709135);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Martin',dmetaphone('Martin'), dmetaphone_alt('Martin'), 0.006303244211);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Roy',dmetaphone('Roy'), dmetaphone_alt('Roy'), 0.006216409597);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Gagnon',dmetaphone('Gagnon'), dmetaphone_alt('Gagnon'), 0.005852226737);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Lee',dmetaphone('Lee'), dmetaphone_alt('Lee'), 0.00573562222);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Wilson',dmetaphone('Wilson'), dmetaphone_alt('Wilson'), 0.005690520472);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Johnson',dmetaphone('Johnson'), dmetaphone_alt('Johnson'), 0.005465286746);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('MacDonald',dmetaphone('MacDonald'), dmetaphone_alt('MacDonald'), 0.005415372312);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Taylor',dmetaphone('Taylor'), dmetaphone_alt('Taylor'), 0.004917534274);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Campbell',dmetaphone('Campbell'), dmetaphone_alt('Campbell'), 0.004886114306);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Anderson',dmetaphone('Anderson'), dmetaphone_alt('Anderson'), 0.004837781184);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Jones',dmetaphone('Jones'), dmetaphone_alt('Jones'), 0.004689619193);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Leblanc',dmetaphone('Leblanc'), dmetaphone_alt('Leblanc'), 0.004670574706);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Cote',dmetaphone('Cote'), dmetaphone_alt('Cote'), 0.004568820763);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Williams',dmetaphone('Williams'), dmetaphone_alt('Williams'), 0.004381538507);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Miller',dmetaphone('Miller'), dmetaphone_alt('Miller'), 0.004340080651);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Thompson',dmetaphone('Thompson'), dmetaphone_alt('Thompson'), 0.004331349063);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Gauthier',dmetaphone('Gauthier'), dmetaphone_alt('Gauthier'), 0.004196181326);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('White',dmetaphone('White'), dmetaphone_alt('White'), 0.003903226226);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Morin',dmetaphone('Morin'), dmetaphone_alt('Morin'), 0.003866718561);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Wong',dmetaphone('Wong'), dmetaphone_alt('Wong'), 0.003792740695);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Young',dmetaphone('Young'), dmetaphone_alt('Young'), 0.003712231326);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Bouchard',dmetaphone('Bouchard'), dmetaphone_alt('Bouchard'), 0.003682461422);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Scott',dmetaphone('Scott'), dmetaphone_alt('Scott'), 0.003515461202);
INSERT INTO common_last_name(last_name, last_name_phonetic, last_name_phonetic_alt, ratio) VALUES ('Stewart',dmetaphone('Stewart'), dmetaphone_alt('Stewart'), 0.003489747706);

Any help would be much appreciated.

Thanks

CodePudding user response：

The issue is this:

END;
$$ language sql;

END; is used in plpgsql in conjunction with BEGIN to mark blocks in the code. sql language functions do not use this syntax as they do not have blocks. The presence of END; results in this error:

ERROR:  return type mismatch in function declared to return record
DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXT:  SQL function "_get_sample_data"